# question

## Help me understand my DATEADD DATEDIFF formula

Can someone help me understand this formula. I'm struggling to get to grips with the DATEDIFF bit Although it does what I want it to and I was able to amend the source code to get a date 14 months ago it doesn't make much sense to me. SELECT DATEADD(month, DATEDIFF(month, -1, GETDATE()) - 14, 0)

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
Break it down to the constituent parts, and it will make sense. When I run this today (6 Jun 2017), it gives me a result of 1 May 2016. Remove the outer DATEADD to leave SELECT DATEDIFF(month, -1, GETDATE()) which returns 1410. This is the number of date boundaries crossed (in this case months) since -1 to getdate(). Datetimes are stored as 2 4-byte integers which means we can actually use numerics to manipulate dates. Here we are using -1 and if you run select cast(-1 as datetime) you will get ----------------------- 1899-12-31 00:00:00.000 (1 row(s) affected) So now the query makes more sense - whats the number of month boundaries crossed between 31 Dec 1899 and getdate() - which for today is 1410. Putting the original query back together this then reads SELECT DATEADD(month, 1410 - 14, 0) which is saying add (1410-14)= 1396 months, to a date of 0 Again 0 can be used to express a date select cast(0 as datetime) ----------------------- 1900-01-01 00:00:00.000 (1 row(s) affected) => so add 1396 months to 1 Jan 1900 = 1 May 2016. This query pattern is used a lot to get dates (stripped of any time portion) but you often use the same base date (i.e. 0) rather than mixing -1 and 0 as your example does. Personally I would write this as SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 13, 0)
1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
Kev Thank you very much for this, the 1410 was really baffling me. it is an awesome explanation Martin
0 Likes 0 ·