Tricky date function

[jbaranski]

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??

[jbaranski]

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

[ck9663]

Don't forget February and leap year.

-- CK