Back to articles list April 2, 2020 - 6 minutes read How to Get the First Day of the Week in SQL Server Agnieszka Kozubek-Krycuń Agnieszka is a Chief Content Officer at Vertabelo. Before coming to Vertabelo, she worked as a Java programmer. She has a PhD in mathematics and over 10 years of experience in teaching mathematics and computer science at the University of Warsaw. In her free time, she enjoys reading a good book, going mountain hiking and practicing yoga. Tags: sql sql basics learn sql 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: WeekRegistrations 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: WeekWeekStartRegistrations 1 2018-01-0158 2 2018-01-07123 ...... ... 53 2019-12-3018 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: DATEFIRSTDATEFIRST meansDays since 1 Jan 1900The date it corresponds to 1Monday 0Monday, January 1, 1900 2Tuesday -6Tuesday, December 26, 1899 3Wednesday-5Wednesday, December 27, 1899 4Thursday -4Thursday, December 28, 1899 5Friday -3Friday, December 29, 1899 6Saturday -2Saturday, December 30, 1899 7Sunday -1Sunday, 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. Tags: sql sql basics learn sql You may also like How to Group Data by Week in SQL Server SQL Server can group data by week. But what does that mean exactly? Is Monday or Sunday the first day of the week? How are the weeks numbered? Read more What Is the Difference Between a GROUP BY and a PARTITION BY? What is the difference between a GROUP BY and a PARTITION BY in SQL queries? When should you use which? You can find the answers in today's article. Read more SQL Window Functions vs. GROUP BY: What’s the Difference? Window functions and GROUP BY may seem similar at first, but they’re quite different. Learn how window functions differ from GROUP BY and aggregate functions. Read more HAVING vs. WHERE in SQL: What You Should Know To take advantage of SQL’s great power, you must understand HAVING vs. WHERE clauses. How do you use them? What are their differences? Read more Difference between GROUP BY and ORDER BY in Simple Words For someone who's learning SQL, one of the most common concepts that they get stuck with is the difference between GROUP BY and ORDER BY. Read more Microsoft SQL Server 2017 Installation Step by Step I’ll show you all the SQL Server 2017 installation steps to help you install it on the Windows operating system together with SQL Management Studio. Read more Microsoft SQL Server Pros and Cons In this entry, we’ll go over the pros and cons of Microsoft SQL Server and evaluate the platform from the perspective of a company and a data specialist. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.