DATEDIFF with the Week Part

Many date-related calculations in SQL Server depend on your language settings. For example, consider the following code asking for the week day number of a date that is a Monday under both us_english and British languages:

SET LANGUAGE us_english;

SELECT DATEPART(weekday, '20091228');

SET LANGUAGE British;

SELECT DATEPART(weekday, '20091228');

This code generates the following output:

Changed language setting to us_english.

-----------

2

Changed language setting to British.

-----------

1

Under us_english your session’s DATEFIRST setting is set implicitly to 7 (meaning that Sunday is the first day of the week), and hence you get the value 2 for a week day of a date that is a Monday. Under British DATEFIRST is set to 1 (meaning Monday is the first day of the week), and hence you get the value 1 as the week day of a date that is a Monday. The user’s effective language setting is reflected in such calculations.

Apparently, things are different with the DATEDIFF function. If you ask for the difference in terms of weeks between two dates, this function will ignore your language setting (and more specifically your DATEFIRST setting), and always perform the calculation assuming Sunday as the first day of the week. As an example, run the following code:

SET LANGUAGE us_english;

SELECT DATEDIFF(week, '20091227', '20091228');

SET LANGUAGE British;

SELECT DATEDIFF(week, '20091227', '20091228');

You will get 0 as the output in both cases; even though under the British language you might expect the result to be 1 since assuming Monday is the first day of the week one week boundary is crossed.

I learned about this just recently and thought this was a bug, since my intuition told me that such a calculation should be language-dependent. Turns out that SQL Server MVP Pawel Potasinski already opened a bug on this item, but it was closed with the following response from Microsoft:

“It looks like this was closed without providing any feedback. The rationale for closing this was that changing the behavior to honor DATEFIRST would make it non-deterministic, and this would prevent building indexes on computed columns or views involving it. This behavior has been around for several versions, so this is a breaking change that doesn't seem worth it.”

Since it looks like Microsoft does not consider it a bug and is not planning to change this behavior I wanted to offer a workaround in case you do need the calculation to be language-dependent. Simply subtract @@DATEFIRST days from both dates in the calculation. The calculation will still assume Sunday as the first day of the week, but the dates the calculation will operate on won’t be the original dates rather manipulated ones. Shifted in exactly the right number of days (@@DATEFIRST) backwards in time such that the calculation will behave as if it is language-dependent.

Here’s how to apply this technique to the dates used in the previous example:

SET LANGUAGE us_english;

SELECT DATEDIFF( week,

  DATEADD(day, -@@DATEFIRST, '20091227'),

  DATEADD(day, -@@DATEFIRST, '20091228') );

SET LANGUAGE British;

SELECT DATEDIFF( week,

  DATEADD(day, -@@DATEFIRST, '20091227'),

  DATEADD(day, -@@DATEFIRST, '20091228') );

This code generates the following output, as expected:

Changed language setting to us_english.

-----------

0

Changed language setting to British.

-----------

1

Cheers,

BG