Tricky date function

I've working on conversion data related to our benefits. Our benefits eligibility date is as such, the first of the month 30 days after your hire date.

ex. Hire Date = 11/5/2008. the 30 grace period ends on 12/5/2008 and the first of the following month, 1/1/2009 (the value i need returned), is when benefits go into effect.

short of VB or stored processes, can pure SQL handle this??

re: Tricky date function

I managed to figure it out. I'm so nice, in fact, that I added a CASE statement just in case their 30 day grace period ends on the first of the month.

CASE
WHEN DAY(DATEADD(d, 30, date)) = '1' THEN DATEADD(d, 30, date)
ELSE DATEADD(dd,-(DAY(DATEADD(mm,1,DATEADD(d, 30, date)))-1),DATEADD(mm,1,DATEADD(d, 30,date)))
END AS SPECIAL_DATE

re: Tricky date function

Don't forget February and leap year.

-- CK