7

I often see queries written with DATEADD and DATEDIFF in the WHERE clause to define a range, flatten datetime to 0 hours, or find the last day of a month or year, but I don't understand how all the parts work.

For example, this will find dates from the start of the current day, to the start of the the day 30 days ago.

SELECT *
FROM tbl
WHERE datecol >= DATEADD(DAY, DATEDIFF(DAY, 0, GETUTCDATE()), 0)
AND datecol   <  DATEADD(DAY, DATEDIFF(DAY, 0, GETUTCDATE()), -30);

What do all the different parts of this accomplish?

11

The best way to understand how date math works is to break a query down into its parts.

Start with this:

SELECT GETUTCDATE() AS utc_date

On this particular day, it returns 2017-10-04 19:34:20.050.

In date math, the number 0 and the date 1900-01-01 are interchangeable.

   SELECT DATEDIFF(DAY, 0, GETUTCDATE()) AS [what does zero mean?],
          DATEDIFF(DAY, '19000101', GETUTCDATE()) AS [it means 1900-01-01]

Which means that you can convert nearly any number to a date. Even negative numbers.

   SELECT CONVERT(DATETIME, -1) AS [how is -1 a date?]

This will bring back 1899-12-31 00:00:00.000, which is the same thing as this:

SELECT DATEADD(DAY, -1, '19000101') AS [zero minus 1 days]

Just subtracting one day from 1900-01-01. Weird, right?

How does this help us?

Let's look inside our original query:

SELECT DATEDIFF(DAY, 0, GETUTCDATE())

That gives us the number of days between 1900-01-01 and the current date. Which means the full expression:

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETUTCDATE()), 0)

Is the adding the number of days between 1900-01-01 and current to 1900-01-01. That gives us the very start of the current day, at 0 hours.

The second part does the same thing, except subtracting 30 days from 1900-01-01, at 0 hours.

DATEADD(DAY, DATEDIFF(DAY, 0, GETUTCDATE()), -30);

If this is all too much to remember, I totally understand.

For a cheat sheet of important date calculations, see Tim Ford's article.

For a calendar table of important dates, see Aaron Bertrand's article.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.