Tuesday, July 18, 2006

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).

But wait, Marc... you said you like to use BETWEEN, but that query doesn't have one... that's because BETWEENis 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:

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.

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