#Whats the week number how to#
#Whats the week number registration#

#Whats the week number how to#

Ultimately, this is the expression we get:īONUS: How to Compute the First Day of the Week Depending on the DATEFIRST Setting Therefore, we have to 'go back' by one day to get the right number of weeks for a date. 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. 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. Let's look at Sunday, 7 January 1900 as an example. The one-day backwards shift is needed because DATEDIFF() uses Sunday as the first day of the week. 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. This is the calendar for January 1900 that DATEDIFF() uses: The DATEDIFF() function treats Sunday as the first day of the week, regardless of the DATEFIRST setting. As you remember, 0 represents midnight on Monday, 1 January 1900. In the expression above, we add the specified number of weeks to the 0 date. SELECT DATEADD(week, DATEDIFF(week, 0, RegistrationDate - 1), 0) AS Monday Now, let's take a look at an expression that returns Monday as the first day of the week: Option 2: Monday as the First Day of the Week

#Whats the week number registration#

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). This expresion: DATEADD(week, DATEDIFF(week, -1, RegistrationDate), -1) AS Sunday The expression DATEDIFF(week, -1, RegistrationDate) computes the number of weeks between Sunday, 31 December 1899 (the date -1) and the RegistrationDate. When used with the week argument, DATEDIFF() works irrespective of the DATEFIRST setting: it always uses Sunday as the first day of the week. It returns the count of the specified dateparts between the startdate and the enddate. DATEDIFF() also takes three arguments: the datepart, the startdate, and the enddate. The number argument in our expression is computed using the DATEDIFF() function. The expression above adds a certain number of weeks to this date. So the date -1 is Sunday, 31 December 1899.

What does that mean? Well, the date 0 is midnight on 1 January 1900, which happens to be Monday. In the expression above, we add a specified number of weeks to the date -1. It then adds a specified number value to the specified datepart of an input date value and subsequently returns that modified value. The function DATEADD() takes three arguments: a datepart, a number, and a date. Here's the expression:ĭATEADD(week, DATEDIFF(week, -1, RegistrationDate), -1) AS Sunday We'll start with Sunday, since it is easier to explain. Option 1: Sunday as the First Day of the Week We'll start with discussing how you can find the first day of the week in either of those styles. 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). However, if you need the precise date for the first day of the week, this won't do. This solution may be good enough if you're simply working interactively with data and need an approximation of when an event happened. 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.

Of course, this solution is by no means perfect it assumes that the events you're counting (in this case, registrations) occur every day. This query shows the number and the minimal registration date of each week (in other words, the first day of the week). The Hack: Using the MIN() Functionīefore 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: In this article, we'll show you how to get the first day of the week in SQL Server. 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. The query displays the week number as the label for the week. The result looks something like this: Week ORDER BY DATEPART(week, RegistrationDate) GROUP BY DATEPART(week, RegistrationDate)

The query might look like this:ĭATEPART(week, RegistrationDate) AS Week, 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.