Help using Date Functions to caluclate new date!

[rougex]

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

 

[gmmastros]

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

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

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

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

 

[rougex]

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

 

[gmmastros]

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

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