467,149 Members | 1,362 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,149 developers. It's quick & easy.

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??
Nov 14 '08 #1
  • viewed: 1129
Share:
2 Replies
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
Nov 14 '08 #2
ck9663
Expert 2GB
Don't forget February and leap year.

-- CK
Nov 14 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Lars Plessmann | last post: by
25 posts views Thread by PyPK | last post: by
1 post views Thread by Pea | last post: by
2 posts views Thread by pruebauno@latinmail.com | last post: by
15 posts views Thread by edouard.spooner@gmail.com | last post: by
28 posts views Thread by birensubudhi@gmail.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.