*This is additional content for the LearnSQL.com course Customer Behavior Analysis in SQL Server.*

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:

Week | Registrations |
---|---|

1 | 58 |

2 | 123 |

... | ... |

52 | 78 |

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:

Week | WeekStart | Registrations |
---|---|---|

1 | 2018-01-01 | 58 |

2 | 2018-01-07 | 123 |

... | ... | ... |

53 | 2019-12-30 | 18 |

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:

DATEFIRST | DATEFIRST means | Days since 1 Jan 1900 | The date it corresponds to |
---|---|---|---|

1 | Monday | 0 | Monday, January 1, 1900 |

2 | Tuesday | -6 | Tuesday, December 26, 1899 |

3 | Wednesday | -5 | Wednesday, December 27, 1899 |

4 | Thursday | -4 | Thursday, December 28, 1899 |

5 | Friday | -3 | Friday, December 29, 1899 |

6 | Saturday | -2 | Saturday, December 30, 1899 |

7 | Sunday | -1 | Sunday, December 31, 1899 |

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.