An interesting thing about the DATEDIFF() function in SQL Server is that it ignores your SET DATEFIRST value.

However, this is not a bug. Microsoft’s documentation for DATEDIFF() clearly states the following:

Specifying SET DATEFIRST has no effect on DATEDIFF. DATEDIFF always uses Sunday as the first day of the week to ensure the function operates in a deterministic way.

In case you don’t know, SET DATEFIRST sets the first day of the week for your session. It’s a number from 1 through 7 (which corresponds to Monday through Sunday).

The initial value for SET DATEFIRST is implicitly set by the language setting (which you can set with the SET LANGUAGE statement). The actual value will depend on the language that is set. For example the default value for the us_english language is 7 (Sunday), whereas the default for the British language is 1 (Monday).

However, you can use a SET DATEFIRST statement to override this so that you can keep using the same language while using a different day for the first day of the week.

But as mentioned, the SET DATEFIRST value has no effect on the DATEDIFF() function. The DATEDIFF() function always assumes that Sunday is the first day of the week regardless of your SET DATEFIRST value.

This can cause some interesting problems when using DATEDIFF() if you don’t know how it works.

If you find yourself in this situation, hopefully the examples on this page can help.

Example 1 – The Problem

First, here’s an example of the actual problem. Note that we can retrieve the SET DATEFIRST value by selecting @@DATEFIRST.

DECLARE 
  @startdate date = '2025-01-05', 
  @enddate date = '2025-01-06';

SET LANGUAGE us_english;
SELECT 
  @@DATEFIRST AS 'SET DATEFIRST Value', 
  DATEDIFF(week, @startdate, @enddate) AS 'us_english DATEDIFF() Result';

SET LANGUAGE British;
SELECT 
  @@DATEFIRST AS 'SET DATEFIRST Value', 
  DATEDIFF(week, @startdate, @enddate) AS 'British DATEDIFF() Result';

Result:

+-----------------------+--------------------------------+
| SET DATEFIRST Value   | us_english DATEDIFF() Result   |
|-----------------------+--------------------------------|
| 7                     | 0                              |
+-----------------------+--------------------------------+

+-----------------------+-----------------------------+
| SET DATEFIRST Value   | British DATEDIFF() Result   |
|-----------------------+-----------------------------|
| 1                     | 0                           |
+-----------------------+-----------------------------+

In this case, the first date falls on a Sunday and the second date on a Monday. Therefore you would normally expect the British DATEDIFF() result to return 1. You would expect this because the week-part boundary is crossed when it goes from Sunday to Monday (because the SET DATEFIRST value is 1 which means “Monday”, and Monday marks the start of a new week).

But because DATEDIFF() ignores your SET DATEFIRST value and assumes that Sunday is the start of the week, we get the same result for both languages.

Just to be sure, I’ll run the query again, but this time I’ll set the SET DATEFIRST value explicitly. In other words, instead of setting the language, I’ll use the SET DATEFIRST statement:

DECLARE 
  @startdate date = '2025-01-05', 
  @enddate date = '2025-01-06';

SET DATEFIRST 7;
SELECT 
  @@DATEFIRST AS 'SET DATEFIRST Value', 
  DATEDIFF(week, @startdate, @enddate) AS 'us_english DATEDIFF() Result';

SET DATEFIRST 1;
SELECT 
  @@DATEFIRST AS 'SET DATEFIRST Value', 
  DATEDIFF(week, @startdate, @enddate) AS 'British DATEDIFF() Result';

Result:

+-----------------------+--------------------------------+
| SET DATEFIRST Value   | us_english DATEDIFF() Result   |
|-----------------------+--------------------------------|
| 7                     | 0                              |
+-----------------------+--------------------------------+

+-----------------------+-----------------------------+
| SET DATEFIRST Value   | British DATEDIFF() Result   |
|-----------------------+-----------------------------|
| 1                     | 0                           |
+-----------------------+-----------------------------+

Same result, even when you explicitly set the SET DATEFIRST value. This is no surprise though – I would be surprised if it didn’t return the same result.

Also, this simply confirms that DATEDIFF() is working exactly as intended.

So, how do we change it so that our DATEDIFF() results honor our SET DATEFIRST value?

The Solution

Here’s a solution/workaround that will allow you to get the intended results. This will ensure that your SET DATEFIRST settings are factored into your DATEDIFF() results.

All you need to do is subtract @@DATEFIRST from the input dates.

DECLARE 
  @startdate date = '2025-01-05', 
  @enddate date = '2025-01-06';

SET DATEFIRST 7;
SELECT 
  @@DATEFIRST AS 'SET DATEFIRST Value', 
  DATEDIFF(week, DATEADD(day, -@@DATEFIRST, @startdate), DATEADD(day, -@@DATEFIRST, @enddate)) AS 'us_english DATEDIFF() Result';

SET DATEFIRST 1;
SELECT 
  @@DATEFIRST AS 'SET DATEFIRST Value', 
  DATEDIFF(week, DATEADD(day, -@@DATEFIRST, @startdate), DATEADD(day, -@@DATEFIRST, @enddate)) AS 'British DATEDIFF() Result';

Result:

+-----------------------+--------------------------------+
| SET DATEFIRST Value   | us_english DATEDIFF() Result   |
|-----------------------+--------------------------------|
| 7                     | 0                              |
+-----------------------+--------------------------------+

+-----------------------+-----------------------------+
| SET DATEFIRST Value   | British DATEDIFF() Result   |
|-----------------------+-----------------------------|
| 1                     | 1                           |
+-----------------------+-----------------------------+

This uses the DATEADD() function to reduce the input dates by the amount of @@DATEFIRST (which is your SET DATEFIRST value).

In this case the DATEDIFF() function still uses Sunday as the first day of the week, however, the actual dates used in the calculation are different. They’ve been moved back in time by the amount of @@DATEFIRST.

The following example shows the dates that were used in the calculation:

DECLARE 
  @startdate date = '2025-01-05', 
  @enddate date = '2025-01-06';

SET DATEFIRST 7;
SELECT 
  @startdate AS 'Original Date', 
  @@DATEFIRST AS 'Subtract By',
  DATEADD(day, -@@DATEFIRST, @startdate) AS 'Resulting Date'
UNION ALL
SELECT 
  @enddate, 
  @@DATEFIRST,
  DATEADD(day, -@@DATEFIRST, @enddate);  

SET DATEFIRST 1;
SELECT 
  @startdate AS 'Original Date', 
  @@DATEFIRST AS 'Subtract By',
  DATEADD(day, -@@DATEFIRST, @startdate) AS 'Resulting Date'
UNION ALL
SELECT 
  @enddate, 
  @@DATEFIRST,
  DATEADD(day, -@@DATEFIRST, @enddate);  

Result:

+-----------------+---------------+------------------+
| Original Date   | Subtract By   | Resulting Date   |
|-----------------+---------------+------------------|
| 2025-01-05      | 7             | 2024-12-29       |
| 2025-01-06      | 7             | 2024-12-30       |
+-----------------+---------------+------------------+

+-----------------+---------------+------------------+
| Original Date   | Subtract By   | Resulting Date   |
|-----------------+---------------+------------------|
| 2025-01-05      | 1             | 2025-01-04       |
| 2025-01-06      | 1             | 2025-01-05       |
+-----------------+---------------+------------------+

So in our workaround, DATEDIFF() used the “Resulting Date” in its calculations.

If you’ve been running into problems with DATEDIFF() ignoring SET DATEFIRST, hopefully this article helped.