DateDiff sql function

Question

DateDiff sql function

shifty-922587

SSC Enthusiast

Points: 162
More actions
January 21, 2010 at 11:23 am

#144372

I was playing around with some code for showing the date and added the value -10 to this query:
DATEADD(mm, DATEDIFF(mm,-10,emp.hiredt), 0)
end) as eligdt
I was told that this slot is the start date and that if i put a number in there it is the milliseconds past january 1st, 1900. This query will output the next month first day. I am not sure why though.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

shifty-922587 (1/21/2010)
I was playing around with some code for showing the date and added the value -10 to this query:
DATEADD(mm, DATEDIFF(mm,-10,emp.hiredt), 0)
end) as eligdt
I was told that this slot is the start date and that if i put a number in there it is the milliseconds past january 1st, 1900. This query will output the next month first day. I am not sure why though.

I have to ask, what are you trying to accomplish here?

Also, if you lookup DATEADD in BOL (Books Online), it will tell you what mm means, and it stands for month.

shifty-922587 SSC Enthusiast Points: 162 More actions · Answer 2

I was actually trying to pull in the date (emp.hiredt), and calculate the next month first date.

I would like to know if I was told correctly about the bolded section and why it would allow -10 ms (if the definition was correct).

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

shifty-922587 (1/21/2010)
I was actually trying to pull in the date (emp.hiredt), and calculate the next month first date.
I would like to know if I was told correctly about the bolded section and why it would allow -10 ms (if the definition was correct).

Actually, the -10 is not ms, but 10 days prior to the 0 (zero) day (1900-01-01) which is 1899-12-22. This means the datediff is calculating the number of months between 1899-12-22 and the emp.hiredt. The dateadd is then adding the number of months to the 1900-01-01, which then returns the first day of the month follwing the hiredt.

Another way to accomplish the same thing is this:

dateadd(mm, datediff(mm, 0, emp.hiredt) + 1, 0)

In this case we are add 1 to the number of months between 1900-01-01 and the hiredt.

shifty-922587 SSC Enthusiast Points: 162 More actions · Answer 4

shifty-922587

SSC Enthusiast

Points: 162

January 21, 2010 at 11:42 am

#1106881

Thank you! That makes more sense. 😀

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

You can find a few more of these types of date calculations here: Some Common Date Routines.