Comment obtenir le premier jour de la semaine dans SQL Server

Il s'agit d'un contenu supplémentaire pour le cours LearnSQL.com Customer Behavior Analysis in SQL Server.

Dans l'article précédent, nous avons expliqué comment utiliser la fonction SQL Server DATEPART() avec week ou iso_week pour regrouper les événements par semaine. La requête peut ressembler à ceci :

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);

Le résultat ressemble à ceci :

La requête affiche le numéro de semaine comme étiquette pour la semaine. Cette étiquette n'est pas très utile. Après tout, comment savoir ce que signifie "semaine 22" ? Est-ce en avril, mai ou juin ? Il est préférable d'afficher une date associée à chaque semaine, c'est-à-dire son premier jour.

Dans cet article, nous allons vous montrer comment obtenir le premier jour de la semaine dans SQL Server. Tout d'abord, un raccourci.

Le Hack : Utilisation de la fonction MIN()

Avant de discuter des moyens appropriés pour calculer le premier jour de la semaine dans SQL Server, parlons d'une astuce que vous pouvez utiliser pour afficher une étiquette lisible pour une semaine - la fonction MIN():

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);

Le résultat ressemblera à ceci :

Cette requête affiche le numéro et la date d'enregistrement minimale de chaque semaine (en d'autres termes, le premier jour de la semaine). Bien entendu, cette solution est loin d'être parfaite ; elle suppose que les événements que vous comptez (dans ce cas, les inscriptions) se produisent tous les jours. S'il n'y a pas d'inscriptions le premier jour de la semaine, la requête vous montrera le deuxième jour de la semaine sous l'étiquette WeekStart.

Cette solution peut être suffisante si vous travaillez simplement de manière interactive avec des données et que vous avez besoin d'une approximation du moment où un événement s'est produit. Cependant, si vous avez besoin de la date précise du premier jour de la semaine, cela ne suffira pas. Essayons autre chose.

Comment calculer les étiquettes de semaine dans le serveur SQL

Il existe deux façons courantes de définir le premier jour de la semaine : le dimanche (généralement utilisé aux États-Unis) et le lundi (généralement utilisé en Europe). Nous allons commencer par examiner comment vous pouvez trouver le premier jour de la semaine dans l'un ou l'autre de ces styles.

Option 1 : le dimanche comme premier jour de la semaine

Nous allons commencer par le dimanche, car il est plus facile à expliquer. Voici l'expression :

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

La fonction DATEADD() prend trois arguments : une partie de date, un nombre et une date. Elle ajoute ensuite une valeur numérique spécifiée à la partie de date spécifiée d'une valeur de date entrée et renvoie ensuite cette valeur modifiée.

Dans l'expression ci-dessus, nous ajoutons un nombre de semaines spécifié à la date -1. Qu'est-ce que cela signifie ? Eh bien, la date 0 est à minuit le 1er janvier 1900, qui se trouve être un lundi. La date -1 est donc le dimanche 31 décembre 1899. L'expression ci-dessus ajoute un certain nombre de semaines à cette date.

L'argument numérique de notre expression est calculé à l'aide de la fonction DATEDIFF(). DATEDIFF() prend également trois arguments : la partie de la date, la date de début et la date de fin. Elle renvoie le nombre de parties de date spécifiées entre la date de début et la date de fin. Lorsqu'elle est utilisée avec l'argument week, DATEDIFF() fonctionne indépendamment du paramètre DATEFIRST: elle utilise toujours le dimanche comme premier jour de la semaine.

L'expression DATEDIFF(week, -1, RegistrationDate) calcule le nombre de semaines entre le dimanche 31 décembre 1899 (la date -1) et le RegistrationDate.

Cette expression :

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

... prend le nombre de semaines entre le dimanche 31 décembre 1899 et la date d'enregistrement, ajoute ce nombre à ce dimanche, et renvoie finalement le dimanche (en d'autres termes, le début de la semaine où l'enregistrement a eu lieu).

Option 2 : le lundi comme premier jour de la semaine

Examinons maintenant une expression qui renvoie le lundi comme premier jour de la semaine :

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

Dans l'expression ci-dessus, nous ajoutons le nombre de semaines spécifié à la date 0. Comme vous vous en souvenez, 0 représente minuit le lundi 1er janvier 1900.

La fonction DATEDIFF() traite le dimanche comme le premier jour de la semaine, indépendamment du paramètre DATEFIRST. C'est le calendrier de janvier 1900 qu'utilise DATEDIFF():

L'expression DATEDIFF(week, 0, RegistrationDate - 1) calcule le nombre de semaines entre le lundi 1er janvier 1900 (date 0) et un jour avant la RegistrationDate. Le décalage d'un jour en arrière est nécessaire car DATEDIFF() utilise le dimanche comme premier jour de la semaine.

Prenons l'exemple du dimanche 7 janvier 1900. L'expression DATEDIFF(week, 0, '19000107') renvoie 1 ; si l'on considère le dimanche comme le premier jour de la semaine, le 7 janvier se trouve dans la semaine 2. Cependant, nous aimerions que le 7 janvier soit traité comme s'il se trouvait dans la semaine 1 - comme si le lundi était le premier jour de la semaine. Par conséquent, nous devons "revenir en arrière" d'un jour pour obtenir le bon nombre de semaines pour une date.

Au final, voici l'expression que nous obtenons :

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

BONUS : Comment calculer le premier jour de la semaine en fonction du réglage de DATEFIRST

Si vous voulez voir une expression qui fonctionne correctement pour tous les paramètres de DATEFIRST, j'ai de mauvaises nouvelles pour vous : ce n'est pas joli. En pratique, vous ne considérerez probablement que le lundi ou le dimanche comme le premier jour de la semaine. Mais, pour l'exhaustivité de cet article, voici une expression qui fonctionnera correctement quel que soit le paramètre DATEFIRST:

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

Tout d'abord, expliquons que @@DATEFIRST-8)%7 traduit la valeur de DATEFIRST par le jour de la semaine approprié au tournant de 1899/1900. (Vous pourriez trouver une autre expression qui calcule la même valeur.) Voici un tableau qui vous montre combien de jours se sont écoulés depuis ce moment :

L'autre expression est

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

Cette expression calcule la différence entre le jour de la semaine choisi et la date d'enregistrement.

Comme DATEDIFF() utilise le dimanche comme premier jour de la semaine, nous devons soustraire la valeur de DATEFIRST de la date d'enregistrement. Nous "décalons" les jours qui, selon DATEDIFF(), tombent dans la semaine suivante.

Ainsi, pour DATEFIRST = 3 (mercredi), le dimanche, le lundi et le mardi doivent être "décalés" à la semaine précédente pour que DATEDIFF() fonctionne comme nous le souhaitons.