Obtenir le premier jour de la semaine dans SQL Server

Question

J'essaie de regrouper les enregistrements par semaine, en stockant la date agrégée comme le premier jour de la semaine. Cependant, la technique standard que j'utilise pour arrondir les dates ne semble pas fonctionner correctement avec les semaines (alors qu'elle le fait pour les jours, les mois, les années, les trimestres et toute autre période à laquelle je l'ai appliquée).

Voici le SQL :

select "start_of_week" = dateadd(week, datediff(week, 0, getdate()), 0);

Ce retour 2011-08-22 00:00:00.000 qui est un lundi et non un dimanche. Sélection de @@datefirst renvoie à 7 qui est le code pour le dimanche, donc le serveur est correctement configuré pour autant que je sache.

Je peux contourner cela assez facilement en changeant le code ci-dessus en :

select "start_of_week" = dateadd(week, datediff(week, 0, getdate()), -1);

Mais le fait que je doive faire une telle exception me met un peu mal à l'aise. Par ailleurs, je m'excuse si cette question fait double emploi. J'ai trouvé quelques questions connexes, mais aucune n'abordait spécifiquement cet aspect.

Answer 1

Pour répondre à la question de savoir pourquoi vous recevez un lundi et non un dimanche :

Vous ajoutez un nombre de semaines à la date 0. Quelle est la date 0 ? 1900-01-01. Quel était le jour en 1900-01-01 ? Lundi. Donc, dans votre code, vous dites : combien de semaines se sont écoulées depuis le lundi 1er janvier 1900 ? Appelons cela [n]. Ok, maintenant ajoutez [n] semaines au lundi 1er janvier 1900. Vous ne devriez pas être surpris que cela finisse par être un lundi. DATEADD n'a aucune idée que vous voulez ajouter des semaines mais seulement jusqu'à ce que vous arriviez à un dimanche, c'est juste ajouter 7 jours, puis ajouter 7 autres jours, ... tout comme DATEDIFF ne reconnaît que les limites qui ont été franchies. Par exemple, ils renvoient tous deux 1, même si certaines personnes se plaignent qu'il devrait y avoir une certaine logique intégrée pour arrondir à l'unité supérieure ou inférieure :

SELECT DATEDIFF(YEAR, '2010-01-01', '2011-12-31');
SELECT DATEDIFF(YEAR, '2010-12-31', '2011-01-01');

Pour répondre à comment obtenir un dimanche :

Si vous voulez un dimanche, choisissez une date de base qui n'est pas un lundi mais plutôt un dimanche. Par exemple :

DECLARE @dt DATE = '1905-01-01';
SELECT [start_of_week] = DATEADD(WEEK, DATEDIFF(WEEK, @dt, CURRENT_TIMESTAMP), @dt);

Cela ne se brisera pas si vous changez votre DATEFIRST (ou votre code est exécuté pour un utilisateur avec un paramètre différent) - à condition que vous vouliez toujours un dimanche, quel que soit le paramètre actuel. Si vous voulez que ces deux réponses concordent, vous devez utiliser une fonction qui fait dépendent de la DATEFIRST le cadre, par exemple

SELECT DATEADD(DAY, 1-DATEPART(WEEKDAY, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP);

Donc si vous changez votre DATEFIRST à lundi, mardi, etc., le comportement changera. En fonction du comportement que vous souhaitez, vous pouvez utiliser l'une de ces fonctions :

CREATE FUNCTION dbo.StartOfWeek1 -- always a Sunday
(
    @d DATE
)
RETURNS DATE
AS
BEGIN
    RETURN (SELECT DATEADD(WEEK, DATEDIFF(WEEK, '19050101', @d), '19050101'));
END
GO

...ou...

CREATE FUNCTION dbo.StartOfWeek2 -- always the DATEFIRST weekday
(
    @d DATE
)
RETURNS DATE
AS
BEGIN
    RETURN (SELECT DATEADD(DAY, 1-DATEPART(WEEKDAY, @d), @d));
END
GO

Vous avez maintenant de nombreuses alternatives, mais laquelle est la plus performante ? Je serais surpris qu'il y ait des différences majeures, mais j'ai rassemblé toutes les réponses fournies jusqu'à présent et je les ai soumises à deux séries de tests - l'une bon marché et l'autre coûteuse. J'ai mesuré les statistiques du client car je ne pense pas que les entrées/sorties ou la mémoire jouent un rôle dans les performances (bien qu'elles puissent entrer en jeu selon la façon dont la fonction est utilisée). Dans mes tests, les résultats sont les suivants :

Demande d'affectation "bon marché" :

Function - client processing time / wait time on server replies / total exec time
Gandarez     - 330/2029/2359 - 0:23.6
me datefirst - 329/2123/2452 - 0:24.5
me Sunday    - 357/2158/2515 - 0:25.2
trailmax     - 364/2160/2524 - 0:25.2
Curt         - 424/2202/2626 - 0:26.3

Demande d'affectation "coûteuse" :

Function - client processing time / wait time on server replies / total exec time
Curt         - 1003/134158/135054 - 2:15
Gandarez     -  957/142919/143876 - 2:24
me Sunday    -  932/166817/165885 - 2:47
me datefirst -  939/171698/172637 - 2:53
trailmax     -  958/173174/174132 - 2:54

Je peux vous communiquer les détails de mes tests si vous le souhaitez - je m'arrête ici car je suis déjà très long. J'ai été un peu surpris de voir que Curt est le plus rapide à l'extrémité supérieure, étant donné le nombre de calculs et de code en ligne. Peut-être que je vais faire des tests plus approfondis et en faire un blog... si vous n'avez pas d'objection à ce que je publie vos fonctions ailleurs.

Answer 2

Pour ceux qui ont besoin d'obtenir :

Lundi = 1 et dimanche = 7 :

SELECT 1 + ((5 + DATEPART(dw, GETDATE()) + @@DATEFIRST) % 7);

Dimanche = 1 et samedi = 7 :

SELECT 1 + ((6 + DATEPART(dw, GETDATE()) + @@DATEFIRST) % 7);

Plus haut, il y avait un exemple similaire, mais grâce au double "%7", il serait beaucoup plus lent.

Answer 3

Cela fonctionne à merveille pour moi :

CREATE FUNCTION \[dbo\].\[StartOfWeek\]
(
  @INPUTDATE DATETIME
)
RETURNS DATETIME

AS
BEGIN
  -- THIS does not work in function.
  -- SET DATEFIRST 1 -- set monday to be the first day of week.

  DECLARE @DOW INT -- to store day of week
  SET @INPUTDATE = CONVERT(VARCHAR(10), @INPUTDATE, 111)
  SET @DOW = DATEPART(DW, @INPUTDATE)

  -- Magic convertion of monday to 1, tuesday to 2, etc.
  -- irrespect what SQL server thinks about start of the week.
  -- But here we have sunday marked as 0, but we fix this later.
  SET @DOW = (@DOW + @@DATEFIRST - 1) %7
  IF @DOW = 0 SET @DOW = 7 -- fix for sunday

  RETURN DATEADD(DD, 1 - @DOW,@INPUTDATE)

END

Answer 4

Peut-être que tu as besoin de ça :

SELECT DATEADD(DD, 1 - DATEPART(DW, GETDATE()), GETDATE())

Ou

DECLARE @MYDATE DATETIME
SET @MYDATE = '2011-08-23'
SELECT DATEADD(DD, 1 - DATEPART(DW, @MYDATE), @MYDATE)

Fonction

CREATE FUNCTION [dbo].[GetFirstDayOfWeek]
( @pInputDate    DATETIME )
RETURNS DATETIME
BEGIN

SET @pInputDate = CONVERT(VARCHAR(10), @pInputDate, 111)
RETURN DATEADD(DD, 1 - DATEPART(DW, @pInputDate),
               @pInputDate)

END
GO

Answer 5

J'ai cherché sur Google ce script :

create function dbo.F_START_OF_WEEK
(
    @DATE           datetime,
    -- Sun = 1, Mon = 2, Tue = 3, Wed = 4
    -- Thu = 5, Fri = 6, Sat = 7
    -- Default to Sunday
    @WEEK_START_DAY     int = 1 
)
/*
Find the fisrt date on or before @DATE that matches 
day of week of @WEEK_START_DAY.
*/
returns     datetime
as
begin
declare  @START_OF_WEEK_DATE    datetime
declare  @FIRST_BOW     datetime

-- Check for valid day of week
if @WEEK_START_DAY between 1 and 7
    begin
    -- Find first day on or after 1753/1/1 (-53690)
    -- matching day of week of @WEEK_START_DAY
    -- 1753/1/1 is earliest possible SQL Server date.
    select @FIRST_BOW = convert(datetime,-53690+((@WEEK_START_DAY+5)%7))
    -- Verify beginning of week not before 1753/1/1
    if @DATE >= @FIRST_BOW
        begin
        select @START_OF_WEEK_DATE = 
        dateadd(dd,(datediff(dd,@FIRST_BOW,@DATE)/7)*7,@FIRST_BOW)
        end
    end

return @START_OF_WEEK_DATE

end
go

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307