DATETIME Calculations, Part 2

Last month, in "DATETIME Calculations, Part 1," I began a multipart discussion of datetime calculations. I talked about the challenges involved with having no separation between the date and time, and I shared techniques for extracting only the date or only the time by zeroing the other part.

This month, I continue the discussion, sharing techniques for calculating a certain period's start and end datetime values—for example, given an input datetime value, returning the corresponding first day of the month. I also share techniques for calculating a language-independent weekday.

Start/End Datetime Values

Last month, I showed you the following expression, which extracts only the date out of a given datetime value by zeroing the time (making it midnight). Again, I'll use the GETDATE() function as the input datetime value:

SELECT DATEADD(day,DATEDIFF(day, 0, GETDATE()),0);

The DATEDIFF function calculates the difference in terms of days between an anchor datetime value—0, representing January 1, 1900, midnight—and the input datetime value—GETDATE(). Call that difference diff. The DATEADD function then adds diff days to the anchor datetime value. Because the anchor's time is midnight, and you add whole days, you get the target date at midnight.

You can use similar logic to calculate a period's start/end datetime values corresponding to a given input datetime value. For example, to calculate the first day of the month, provide an anchor date that is a first day of a month, and instead of using day units, use month units:

SELECT DATEADD(month,DATEDIFF(month, 0, GETDATE()),0);

Remember that 0 represents the base date January 1, 1900. The DATEDIFF function calculates the difference in terms of months between the anchor and the input datetime value (call that difference diff ). The DATEADD function then adds diff months to the anchor datetime value. Because the anchor's day unit is 1 (the first of the month), and you add whole months, you get the first day of the month corresponding to the input datetime value. To get the last day of the month, simply add diff plus one more month, and finally subtract one day:

SELECT DATEADD( month, DATEDIFF(month, 0,   GETDATE()) + 1,  0) - 1;

Adding diff plus one month produces the first day of the next month. Subtracting one day produces the last day of the current month.

Similarly, you can calculate the first day of the year. Simply specify a year unit instead of month:

SELECT DATEADD(year,DATEDIFF(year, 0,    GETDATE()),0);

To calculate the last day of the year, use

SELECT DATEADD(year,DATEDIFF(year, 0,   GETDATE()) + 1,  0) - 1;

To calculate the start of the hour (zeroing the minutes, seconds, and milliseconds), use an hour unit:

SELECT DATEADD(hour,DATEDIFF(hour, 0, GETDATE()),0);

To calculate the last minute of the hour, use

SELECT DATEADD( minute, -1,  DATEADD(  hour,  DATEDIFF(hour, 0, GETDATE()) +1,  0) );

In previous calculations of "last" values (e.g., last day of the month), you simply used the minus operator to subtract 1 day. There was no need to use the DATEADD function to subtract a day because day is the default unit assumed with the use of a plus or minus operator to add or subtract an integer from a datetime value. Here, you need to subtract a minute from the next hour, so you use the DATEADD function for this purpose.

Language-Independent Weekday

Another common need in datetime calculations is to calculate the weekday of a given datetime value. You would need this calculation, for example, to return all orders placed on a Tuesday. It sounds like a simple task for the DATENAME function, as follows:

USE Northwind; SELECT OrderID, OrderDate, CustomerID, EmployeeID FROM dbo.Orders WHERE DATENAME(weekday, OrderDate) = 'Tuesday';

However, this code will work correctly only if the effective language setting of the session running the code is English. Try running this code in a session in which the effective language is, for example, Italian, and you'll get back an empty set

SET Language Italian; 

SELECT OrderID, OrderDate, CustomerID, EmployeeID FROM dbo.Orders WHERE DATENAME(weekday, OrderDate) = ‘Tuesday';

If you want your application to serve inter- national users, you'll want your code to work correctly regardless of the effective language setting. In other words, you want your expressions to be language-independent.

One option you might consider is to use the DATEPART function, specifying the weekday part, which returns

a weekday number. The expression DATEPART(weekday, OrderDate) returns the weekday number. However, the weekday number is dependent on the session's DATEFIRST (first day of the week) setting, which in turn is dependent on the login's language setting. For example, if the login's language setting is us_english, the DATEFIRST setting is implicitly set to 7 (which means Sunday is the first day of the week). So, in a session in which the effective language is us_english, you'll get 3 for a Tuesday. Your query for orders placed on Tuesdays would look like

SELECT OrderID, OrderDate,  CustomerID, EmployeeIDFROM dbo.Orders WHERE DATEPART(weekday,OrderDate) = 3;

If the login's language is Italian, the DATEFIRST setting is implicitly set to 1 (which means Monday is the first day of the week), so the expression DATEPART(weekday, OrderDate) will return 2 for a Tuesday. Therefore, to get all orders placed on Tuesdays, you'll need to run the following query:

SELECT OrderID, OrderDate,  CustomerID, EmployeeIDFROM dbo.Orders WHERE DATEPART(weekday,OrderDate) = 2;

To know exactly what weekday number to expect, you can explicitly change the DATEFIRST setting by using the SET DATEFIRST command. However, you might prefer not to do so. Running that command can affect other code running in the session and can affect performance in other ways that are outside the scope of this article.

In short, the expression DATEPART (weekday, OrderDate) is language-dependent, and if your application serves international users, you need to be careful. You should write your expressions so that they're language-independent.

One way to calculate a weekday number in a language-independent manner is to rely on the fact that you have access to the current effective value of the DATEFIRST setting via the @@DATEFIRST function. Instead of using the expression DATEPART (weekday,orderDate), use the expression DATEPART(weekday, OrderDate+ @@ DATEFIRST - logical_datefirst_constant). The trick is to add @@DATEFIRST days to the input datetime value, thereby neutralizing the effect of the DATEFIRST setting. Think about it: If DATEFIRST is set to some n value, you add n days to the date you're checking. This way, the weekday number you get won't depend on the DATEFIRST setting. The role of logical_datefirst_constant is to let you control what will be the logical DATEFIRST value you want to use. For example, if you want to logically set the DATEFIRST value to Monday, subtract the constant 1 (which represents Monday) as the DATEFIRST value.

Try running the following expression multiple times, setting the DATEFIRST setting to different values. You'll see that you always get the same weekday number (today's), assuming Monday is the first day of the week.

SET DATEFIRST 1;(also try with 2, 3, 4, 5, 6, 7)SELECT DATEPART(weekday,  GETDATE() + @@DATEFIRST - 1);

If you want to consider Sunday as the first day of the week, subtract the constant 7 (representing Sunday):

SELECT DATEPART(weekday, GETDATE() + @@DATEFIRST - 7);

(Again, this calculation is independent of the DATEFIRST setting.) So, to get all orders placed on Tuesdays, you can use the following code (setting Monday as the logical first day of the week):

SELECT OrderID, OrderDate,  CustomerID, EmployeeID   FROM dbo.Orders WHERE DATEPART(weekday, OrderDate+ @@DATEFIRST - 1) = 2;

This code is independent of any languagerelated settings that are in effect in the session.

There's another elegant technique for calculating a language-independent weekday number. I learned this technique from SQL Server MVP Steve Kass. Pick a constant date whose weekday is what you want to logically consider as the first day of the week. Call this date anchor. For this purpose, it's convenient to remember that the base date January 1, 1900—represented by the integer 0—is a Monday. So, if you want to consider Monday the first day of the week, use the value 0; for Tuesday, use 1; for Wednesday, use 2; and so on. Using the DATEDIFF function, calculate the difference in terms of days between the anchor date and the input date. Call that difference diff. The expression diff % 7 naturally will return 0 if the input date has the same weekday as the anchor date. More generally, for an input date with a weekday number n (in respect to the logical date you chose first) the expression diff % 7 will return n-1. So, to get the weekday number n, you need to use the expression diff % 7 + 1. Try it, using the anchor 0 to make Monday the first day of the week:

SET DATEFIRST 1;(also try with 2, 3, 4, 5, 6, 7)SELECT DATEDIFF(day, 0,  GETDATE()) % 7 + 1;

You'll always get the correct weekday number for today's date, regardless of the DATEFIRST setting.

To get all orders placed on Tuesdays, considering Monday as the logical date first setting, use the query

SELECT OrderID, OrderDate,  CustomerID, EmployeeID   FROM dbo.Orders WHERE DATEDIFF(day, 0, OrderDate)% 7 + 1 = 2;

If you're curious about which of the two techniques to calculate a language-independent weekday number is faster, run the code in Web Listing 1. This code runs each technique one million times. I got the results in Table 1, which shows that the second technique is faster than the first.

Always Simplifying

To save yourself grief, you must be able to perform datetime-related calculations in a manner that's independent of any language settings. Always keep in mind that users of your application might be running with different language-related settings. Next month, I'll discuss other datetime-related calculations.

Related: DATETIME Calculations, Part 3