More on DATEs and SQL

Building on this First/Last day of month post (oddly, one of the main ways people seem to land on my blog), here are some other commonly needed SQL queries:

Beginning of period

Midnight of any day (i.e. truncate the time from a date)

SELECT DATEADD(dd, DATEDIFF(dd, 0, TheDate), 0)

This works by subtracting the supplied date (like GetDate() for today) from zero--which Microsoft SQL Server interprets as 1900-01-01 00:00:00 and gives the number of days. This value is then re-added to the zero date yielding the same date with the time truncated.

Midnight of today (i.e. what day is today)

SELECT DATEADD(dd, DATEDIFF(dd, 0, GetDate()), 0)

You can also use GetUTCDate() if you are a good developer and are storing everything in UTC.

Monday of any week

SELECT DATEADD(wk, DATEDIFF(wk, 0, TheDate), 0)

This assumes that Sunday is first day of the week. Again, you can use GetDate() or GetUTCDate() for TheDate.

First Day of the Month

SELECT DATEADD(mm, DATEDIFF(mm, 0, TheDate), 0)

This one uses mm to extract the month-only portion of the date just like dd above extracted the date portion.

First Day of the Quarter

SELECT DATEADD(qq, DATEDIFF(qq, 0, TheDate), 0)

Playing the exact same game with quarters yields the expected value.

First Day of the Year

SELECT DATEADD(yy, DATEDIFF(yy, 0, TheDate), 0)

Once more with the yy to extract the year-only portion.

End of period

Okay, so you need the end of the month, quarter, etc. First, remember that if you are not dealing with "known to be date without time" data, you need to be very careful when doing comparisons against a date. For example, comparing a DATETIME column against a user-entered date is almost guaranteed to be wrong if the column has any time component. This is one of the reasons I always prefer to use a BETWEEN clause, as it forces me to think about the date-as-continuum issues. So, almost always, the best thing to do is compare for <. Now that I've justified my reasoning, I'll tell you that it is much easier to get the next "week", "month", "quarter" or "year" and compare for less-than, instead of getting the last value of the current "whatever". Here's the rest:

Midnight of the next day (i.e. truncate the time from date, then get the next)

SELECT DATEADD(dd, DATEDIFF(dd, 0, TheDate) + 1, 0)

note the new + 1. This we get the current date-count, add one and covert it all back (using GetDate or GetUTCDate() should be obvious by now).

Monday of the next week

SELECT DATEADD(wk, DATEDIFF(wk, 0, TheDate) + 1, 0)

Again assumes that Sunday is first day of the week.

First Day of the next Month

SELECT DATEADD(mm, DATEDIFF(mm, 0, TheDate) + 1, 0)

First Day of the next Quarter

SELECT DATEADD(qq, DATEDIFF(qq, 0, TheDate) + 1, 0)

First Day of the next Year

SELECT DATEADD(yy, DATEDIFF(yy, 0, TheDate) + 1, 0)

Putting it to use

This yields queries like this for orders due this month:

SELECT [ID]
FROM [dbo].[Orders]
WHERE [ShipDue] >= DATEADD(mm, DATEDIFF(mm, 0, GetUTCDate()), 0)
AND [ShipDue] < DATEADD(mm, DATEDIFF(mm, 0, GetUTCDate()) + 1, 0)

But wait, Marc... you said you like to use BETWEEN, but that query doesn't have one... that's because BETWEEN is inclusive, meaning it includes the end-points. If I had an Order that was due at midnight of the first day of the next month it would be included. So how do you get the appropriate value for an end-of-period? It's most certainly NOT by using date-parts to assemble one (but is you must, please remember that it's 23:59:59.997 as a maximum time... don't forget the milliseconds). To do it right, we use the incestuous knowledge that Microsoft SQL Server DATETIME columns have at most a 3 millisecond resolution (something that is not going to change). So all we do is subtract 3 milliseconds from any of those end-of-period formulas given above. For example, the last possible instant of yesterday (local time) is:

SELECT DATEADD(ms, -3, DATEADD(dd, DATEDIFF(dd, 0, GetDate()), 0))

So to do the orders due this month as a BETWEEN query, you can use this:

SELECT [ID]
FROM [dbo].[Orders]
WHERE [ShipDue] BETWEEN DATEADD(mm, DATEDIFF(mm, 0, GetUTCDate()), 0)
AND DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm, 0, GetUTCDate()) + 1, 0))

Remember, always make sure that you do math against input parameters, NOT columns, or you will kill the SARG-ability of the query, which means indexes that might have been used aren't.

Here's the complete pastable list:

SELECT
DATEADD(dd, DATEDIFF(dd, 0, GetDate()), 0) As Today
, DATEADD(wk, DATEDIFF(wk, 0, GetDate()), 0) As ThisWeekStart
, DATEADD(mm, DATEDIFF(mm, 0, GetDate()), 0) As ThisMonthStart
, DATEADD(qq, DATEDIFF(qq, 0, GetDate()), 0) As ThisQuarterStart
, DATEADD(yy, DATEDIFF(yy, 0, GetDate()), 0) As ThisYearStart
, DATEADD(dd, DATEDIFF(dd, 0, GetDate()) + 1, 0) As Tomorrow
, DATEADD(wk, DATEDIFF(wk, 0, GetDate()) + 1, 0) As NextWeekStart
, DATEADD(mm, DATEDIFF(mm, 0, GetDate()) + 1, 0) As NextMonthStart
, DATEADD(qq, DATEDIFF(qq, 0, GetDate()) + 1, 0) As NextQuarterStart
, DATEADD(yy, DATEDIFF(yy, 0, GetDate()) + 1, 0) As NextYearStart
, DATEADD(ms, -3, DATEADD(dd, DATEDIFF(dd, 0, GetDate()) + 1, 0)) As TodayEnd
, DATEADD(ms, -3, DATEADD(wk, DATEDIFF(wk, 0, GetDate()) + 1, 0)) As ThisWeekEnd
, DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm, 0, GetDate()) + 1, 0)) As ThisMonthEnd
, DATEADD(ms, -3, DATEADD(qq, DATEDIFF(qq, 0, GetDate()) + 1, 0)) As ThisQuarterEnd
, DATEADD(ms, -3, DATEADD(yy, DATEDIFF(yy, 0, GetDate()) + 1, 0)) As ThisYearEnd

For general reading about dates and time, might I suggest this post's links.