Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help using Date Functions to caluclate new date!

Status
Not open for further replies.

rougex

Technical User
Nov 7, 2006
19
US
Greetings

I am attempting to find the 1st of the month following 90 days of an employees hire date (EE.HIRE_DT)

So far I have come up with:

DATEPART(MM,(DATEADD(MM,4,DATEADD(DD,-(DATEPART(DD,EE.HIRE_DT)-1),EE.HIRE_DT))) )

However... this isnt working in all cases. For example, if the hire date is 7/2/2007 it is returning 11/1/2007 rather than 10/1/2007.

Can anyone please provide me with some guidance?
Than kyou!!!!
 
If the hire date is 7/2/2007, then adding 90 days, you get. 9/30/2007. So, the first of that month should be 9/1/2007.

To return the first of the month....

Code:
Select DateAdd(Month, DateDiff(Month, 0, GetDate()), 0)

Since you want to add 90 days to the hire date...

Code:
Select DateAdd(Month, DateDiff(Month, 0, DateAdd(Day, 90, EE.HIRE_DT)), 0)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Thank you George

I am however trying to find the first of the month FOLLOWING 90 days of employment, so in your example I would be looking for 10/1/2007??????

Thank you thank you
 
I'm not sure I completely understand, but try this...

Code:
Select DateAdd(Month, [!]1 + [/!]DateDiff(Month, 0, DateAdd(Day, 90, EE.HIRE_DT)), 0)

This may produce inaccurate results if the 90th day falls exactly on the first day of a month, so make sure you test it good.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top