Here are two common pitfalls that you may encounter when it comes to working with dates, specifically with week numbers and weekdays. The first is to mistake week numbers in SQL Server for ISO week numbers (which are the weeknumbers used in Sweden, for instance). The other is to make assumptions about which day of the week is the first. The good news is that these are relatively easy fixes.

Week numbers and ISO week numbers

One of the first things you will undoubtedly encounter if you ever work in Sweden (and perhaps a lot of other countries in the world) is that everyone is uncannily familiar with week numbers. They may ask you what you’re doing on thursday afternoon, week 18, or if you will be on vacation in week 32.

This discussion aside, there are different standards for calculating week numbers. In the US, for instance, weeks begin on sunday, and the first week of the year is the one that contains the first saturday. This is also how SQL Server calculates week numbers, if you use the DATEPART function in the following way:

SELECT DATEPART(ww, [date]);

In Sweden, Norway and other countries, the ISO-8601 standard is used to calculate week numbers. According to the ISO standard, the first day of the week is monday, and the first week of the year is the first week that contains a thursday. This means that some years can actually start with the previous year’s last week, 52 or 53, for a few days. This is obviously more complex than the US system, but the advantage is that you’ll never assign the same week number twice.

In older SQL Server versions, calculating ISO weeknumbers was a pain, involving lots of DATEADD and DATEPART calls in a function. Nowadays, though, there is the new isoww modifier for DATEPART.

SELECT DATEPART(isoww, [date]);

The following query visualizes the differences in week number styles:

WITH n (n)
AS (
    --- Counter, from 0 to 7.
    SELECT 0 UNION ALL
    SELECT n+1 FROM n WHERE n<7),

     dates ([date])
AS (
    --- Show the 7 first days of 7 years
    SELECT DATEADD(dd, d.n, DATEADD(yy, 8+y.n, {d '2000-01-01'}))
    FROM n AS d
    CROSS JOIN n AS y)

--- Display the above dates with weekday name, US week
--- and ISO week number.
SELECT [date],
    DATENAME(dw, [date]) AS [weekday],
    DATEPART(ww, [date]) AS [us week no],
    DATEPART(isoww, [date]) AS [iso week no]
FROM dates;

The above brings us to the next topic, which is not entirely unrelated:

Using DATEFIRST

As we touched on above, in different countries and cultures, weeks start on different days. While the ISO standard and many European countries will think of mondays as the first day of the week, in the US, sunday is considered to be the first day. This has implications when you work with weekdays in SQL Server.

A good solution to handling weekday numbers is to explicitly define (within the context of your code) which day is the first day of the week, using SET DATEFIRST. This setting defines the first day of the week, and is used not only when calculating weeks with DATEPART, but more importantly, when you query the current day of the week.

--- Weeks now start on thursdays.
SET DATEFIRST 4;

WITH aFewDays ([date], n)
AS (
    SELECT CAST(GETDATE() AS date) AS [date], 1 UNION ALL
    SELECT DATEADD(dd, 1, [date]), n+1 FROM aFewDays WHERE n<14)

SELECT
    [date],
    DATENAME(dw, [date]) AS [weekday],
    DATEPART(dw, [date]) AS [day of week],
    DATEPART(ww, [date]) AS [week number]
FROM aFewDays;

The current DATEFIRST setting can be read in the system variable @@DATEFIRST.

SELECT @@DATEFIRST;

The @@DATEFIRST variable is really useful in situations where you cannot use SET DATEFIRST, like in user-defined functions (which always inherit the calling context’s settings and cannot contain SET statements).

How to get a “DATEFIRST-neutral” weekday number

As you saw in the example above, SET DATEFIRST affects how the “day of the week” is calculated. If you have legacy code, or for some other reason can’t or don’t want to use SET DATEFIRST, you can use @@DATEFIRST to calculate the offset, so you can correct for it.

Here’s an example of a function that always returns the day of the week as if monday is the first day. So the function will always return the same number, irrespective of your DATEFIRST setting:

/*  Equivalent to:
    SET DATEFIRST 1
    SELECT DATEPART(dw, @date)
*/

SELECT 1+(DATEPART(dw, [date])+@@DATEFIRST-2)%7;

This is how it breaks down:

• Add @@DATEFIRST to the calculated day of the week
• Subtract 2. If you want the week to start on sunday, subtract 1!
• Now, use the modulo operator to “wrap” weekdays, so weekday 8 becomes 1, 9 becomes 2, etc.
• The result will now contain range 0-6, which we’ll simply correct by adding 1 outside the modulo expression.

Even better, you could build a user-defined function to do the work for you:

CREATE FUNCTION dbo.fn_dayOfWeek(
    @date            date,     --- The date
    @firstDayOfWeek  tinyint=1 --- First day of week (1-7)
)
RETURNS tinyint                --- Returns day of week (1-7)
AS

BEGIN
    RETURN 1+(
            DATEPART(dw, @date)
            +@@DATEFIRST
            +(6-@firstDayOfWeek)
        )%7
END

Hope this is useful to you. Let me know in the comments section below, if there’s anything you’re missing!