By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,295 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

Tricky date function

P: 16
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
Share this Question
Share on Google+
2 Replies


P: 16
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 2.5K+
P: 2,878
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.