How to Get the First Day of the Week in SQL Server

This is additional content for the LearnSQL.com course Customer Behavior Analysis in SQL Server. This interactive course will teach you how to analyze customer lifecycle in a SQL database in over 70 hands-on exercises. You will learn how to find out compare registration rates between periods of time, compute conversion rates in SQL, compare customer cohorts, analyze customer activity and customer churn over time. This course is essential for anyone doing customer analysis in their job.

In the previous article, we talked about how you can use the SQL Server DATEPART() function with week or iso_week to group events by week. The query might look like this:

SELECT
  DATEPART(week, RegistrationDate) AS Week,
  COUNT(CustomerID) AS Registrations
FROM Customers
WHERE '20180101' <= RegistrationDate
  AND RegistrationDate < '20190101'
GROUP BY DATEPART(week, RegistrationDate)
ORDER BY DATEPART(week, RegistrationDate);

The result looks something like this:

The query displays the week number as the label for the week. This label is not very helpful. After all, how do we know what "week 22" means? Is it in April, May, or June? It's better to display a date associated with each week, i.e. its first day.

In this article, we'll show you how to get the first day of the week in SQL Server. First up: a shortcut.

The Hack: Using the MIN() Function

Before we discuss the proper ways to compute the first day of the week in SQL Server, let's talk about a trick you can use to show a readable label for a week – the MIN() function:

SELECT
  DATEPART(week, RegistrationDate) AS Week,
  MIN(RegistrationDate) as WeekStart,
  COUNT(CustomerID) AS Registrations
FROM Customers
WHERE '20180101' <= RegistrationDate
  AND RegistrationDate < '20190101'
GROUP BY DATEPART(week, RegistrationDate)
ORDER BY DATEPART(week, RegistrationDate);

The result will look like this:

This query shows the number and the minimal registration date of each week (in other words, the first day of the week). Of course, this solution is by no means perfect; it assumes that the events you're counting (in this case, registrations) occur every day. If there are no registrations on the first day of the week, the query will show you the second day of the week as the WeekStart label.

This solution may be good enough if you're simply working interactively with data and need an approximation of when an event happened. However, if you need the precise date for the first day of the week, this won't do. Let's try something else.

How to Compute Week Labels in SQL Server

There are two common ways to define the first day of the week: Sunday (typically used in the US) and Monday (typically used in Europe). We'll start with discussing how you can find the first day of the week in either of those styles.

Option 1: Sunday as the First Day of the Week

We'll start with Sunday, since it is easier to explain. Here's the expression:

DATEADD(week, DATEDIFF(week, -1, RegistrationDate), -1) AS Sunday;

The function DATEADD() takes three arguments: a datepart, a number, and a date. It then adds a specified number value to the specified datepart of an input date value and subsequently returns that modified value.

In the expression above, we add a specified number of weeks to the date -1. What does that mean? Well, the date 0 is midnight on 1 January 1900, which happens to be Monday. So the date -1 is Sunday, 31 December 1899. The expression above adds a certain number of weeks to this date.

The number argument in our expression is computed using the DATEDIFF() function. DATEDIFF() also takes three arguments: the datepart, the startdate, and the enddate. It returns the count of the specified dateparts between the startdate and the enddate. When used with the week argument, DATEDIFF() works irrespective of the DATEFIRST setting: it always uses Sunday as the first day of the week.

The expression DATEDIFF(week, -1, RegistrationDate) computes the number of weeks between Sunday, 31 December 1899 (the date -1) and the RegistrationDate.

This expresion:

DATEADD(week, DATEDIFF(week, -1, RegistrationDate), -1) AS Sunday

... takes the number of weeks between Sunday, 31 December 1899 and the registration date, adds that number to that Sunday, and ultimately returns Sunday (in other words, the beginning of the week when the registration happened).

Option 2: Monday as the First Day of the Week

Now, let's take a look at an expression that returns Monday as the first day of the week:

SELECT DATEADD(week, DATEDIFF(week, 0, RegistrationDate - 1), 0) AS Monday;

In the expression above, we add the specified number of weeks to the 0 date. As you remember, 0 represents midnight on Monday, 1 January 1900.

The DATEDIFF() function treats Sunday as the first day of the week, regardless of the DATEFIRST setting. This is the calendar for January 1900 that DATEDIFF() uses:

The expression DATEDIFF(week, 0, RegistrationDate - 1) computes the number of weeks between Monday, 1 January 1900 (date 0) and one day before the RegistrationDate. The one-day backwards shift is needed because DATEDIFF() uses Sunday as the first day of the week.

Let's look at Sunday, 7 January 1900 as an example. The expression DATEDIFF(week, 0, '19000107') will return 1; if you treat Sunday as the first day of the week, January 7 is in week 2. However, we would like January 7 to be treated as if it were in week 1 – as if Monday was the first day of the week. Therefore, we have to "go back" by one day to get the right number of weeks for a date.

Ultimately, this is the expression we get:

SELECT DATEADD(week, DATEDIFF(week, 0, RegistrationDate - 1), 0) AS Monday;

BONUS: How to Compute the First Day of the Week Depending on the DATEFIRST Setting

If you want to see an expression that works correctly for all DATEFIRST settings, I have some bad news for you: it's not pretty. In practice, you'll probably only consider Monday or Sunday as the first day of the week. But, for the completeness of this article, here is an expression that will work correctly regardless of DATEFIRST:

SELECT DATEADD(week, 
  DATEDIFF(week, (@@DATEFIRST-8)%7, RegistrationDate-(@@DATEFIRST % 7)), 
  (@@DATEFIRST-8)%7) AS DatefirstAsFirstDayOfWeek;

First, let's explain that @@DATEFIRST-8)%7 translates the value of DATEFIRST to the appropriate day of the week at the turn of 1899/1900. (You could come up with a different expression that computes the same value.) Here's a table to show you how many days have elapsed since that point:

The other expression is

DATEDIFF(week, (@@DATEFIRST-8)%7, RegistrationDate - (@@DATEFIRST % 7)). 

This expression computes the difference between our chosen weekday and the registration date.

Since DATEDIFF() uses Sunday as the first day of the week, we have to subtract the value of DATEFIRST from the registration date. We're "shifting back" the days that, according to DATEDIFF(), fall in the following week.

So, for DATEFIRST = 3 (Wednesday), Sunday, Monday, and Tuesday must be "shifted back" to the preceding week for DATEDIFF() to work as we intend it to.