Tuesday, July 18, 2006

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.

14 comments:

Anonymous said...

Thank you! Very Helpful!!

Anonymous said...

Why the bias against math on columns? For smaller databases losing an index isn't an issue, and results in much simpler queries, just my humble opinion.

Mark Abraham said...

Sweeet tips on the dates...I've just used your advice for truncating the time. The customer wanted only to display the top of the hour.

wsindhu said...

Is it possible that I can store date/time in "datetime" column like this...

"2009-02-06 11:39:25"

Do I have to use something in "formula" field?

Please do help me!

IDisposable said...

SQL Server (and all the others) store DateTime values in an internal binary representation that has NOTHING to do with how it is displayed for you when you make a query. What you need to do is specify the formatting you require in you client layer (where it belongs) or (at worst) in the SELECT statement. That said, your format is perfectly acceptable for an INSERT or UPDATE statement if single-quoted.

Anonymous said...

You are a life saver :) many thanks.

Rocky

Anonymous said...

If you use the suggested;
select dateadd(dd, datediff (dd, 0, '20080327 23:59:59.999'), 0)
It actually returns 20080328 00:00:00.000
It is obviously a rounding issue but is there a way around it?

IDisposable said...

.999 is beyond the precision I stated. You cannot use anything higher than .997 because the resolution of SQL Server is 3 milliseconds.

Guest said...

How about the first weekday of the last month? ;) Anyone?

IDIsposable said...

Step by step
First day of LAST month:
SELECT DATEADD(mm, DATEDIFF(mm, 0, GetDate()) - 1, 0)
First Monday of LAST month OR last Monday of prior month:
SELECT DATEADD(wk, DATEDIFF(wk, 0, DATEADD(mm, DATEDIFF(mm, 0, GetDate()) - 1, 0)), 0)

So all we have to do is deal with when the prior Monday is in the wrong month (and add 7 days)

DECLARE @today DATETIME
SET @today = '2009-08-23 11:56:23' --GetDate()
SELECT CASE WHEN RecentMonday < StartOfLastMonth
THEN
DATEADD(wk, DATEDIFF(wk, 0, RecentMonday), 7) -- adjust up a week
ELSE
RecentMonday
END AS FirstMondayOfTheMonth
FROM (
SELECT DATEADD(mm, DATEDIFF(mm, 0, @today) - 1 , 0) AS StartOfLastMonth
, DATEADD(wk, DATEDIFF(wk, 0, DATEADD(mm, DATEDIFF(mm, 0, @today) - 1, 0)), 0) AS RecentMonday
) AS Fake

Anonymous said...

Thanks for the info !

San said...

TheDate is not working in sql?

Marc Brooks said...

TheDate is meant to be where _YOU_ insert whatever date you want to manipulate... a column name, GETDATE(), etc...

Anonymous said...

Regarding the "Monday of any week", I needed to get the monday of any week, regardless of @@DATEFIRST settings. This the formula I came up with:

DATEADD(DAY,-(DATEPART(weekday, GETDATE()) + @@DATEFIRST +5)%7, GETDATE())


What it does is for instance given tuesday, and datefirst =7 (sunday)
3+7+5=15%7=1, and substracts 1, from the date, which gives the monday.

For datefirst = 1(monday) it would be:
2+1+5=8%7=1 and substract 1, from the date, which gives monday