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.