sql-server - week - sql substring sql server




Obtenir le premier jour de la semaine dans SQL Server (9)

Cela fonctionne merveilleusement 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

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

Voici le SQL:

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

Cela renvoie 2011-08-22 00:00:00.000 , qui est un lundi, pas un dimanche. La sélection de @@datefirst renvoie 7 , ce qui correspond au code de dimanche, de sorte que le serveur est correctement configuré pour autant que je sache.

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

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

Mais le fait que je doive faire une telle exception me rend un peu mal à l'aise. Aussi, excuses s'il s'agit d'une question en double. J'ai trouvé quelques questions connexes mais aucune qui traitait spécifiquement de cet aspect.


Googled 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


J'avais un problème similaire. Étant donné une date, je voulais obtenir la date du lundi de cette semaine.

J'ai utilisé la logique suivante: Trouver le numéro du jour dans la semaine dans la gamme de 0-6, puis soustraire cela de la date d'origine.

J'ai utilisé: DATEADD (jour, - (DATEPART (jour de la semaine,) + 5)% 7,)

Depuis DATEPRRT (jour de la semaine), renvoie 1 = Sundaye ... 7 = samedi, DATEPART (jour de la semaine,) + 5)% 7 renvoie 0 = lundi ... 6 = dimanche.

La soustraction de ce nombre de jours à partir de la date d'origine donne le lundi précédent. La même technique pourrait être utilisée pour n'importe quel jour de départ de la semaine.


Je n'ai aucun problème avec les réponses données ici, mais je pense que le mien est beaucoup plus simple à mettre en œuvre et à comprendre. Je n'ai pas effectué de tests de performance dessus, mais ça devrait être neglegable.

J'ai donc dérivé ma réponse du fait que les dates sont stockées dans le serveur SQL sous forme d'entiers, (je ne parle que du composant date). Si vous ne me croyez pas, essayez cette option SELECT CONVERT (INT, GETDATE ()), et vice versa.

Maintenant, sachant cela, vous pouvez faire quelques équations mathématiques cool. Vous pourriez peut-être en trouver un meilleur, mais voici le mien.

/*
TAKEN FROM http://msdn.microsoft.com/en-us/library/ms181598.aspx
First day of the week is
1 -- Monday
2 -- Tuesday
3 -- Wednesday
4 -- Thursday
5 -- Friday
6 -- Saturday
7 (default, U.S. English) -- Sunday
*/

--Offset is required to compensate for the fact that my @@DATEFIRST setting is 7, the default. 
DECLARE @offSet int, @testDate datetime
SELECT @offSet = 1, @testDate = GETDATE()

SELECT CONVERT(DATETIME, CONVERT(INT, @testDate) - (DATEPART(WEEKDAY, @testDate) - @offSet))

Peut-être que je suis en train de simplifier ici, et c'est peut-être le cas, mais cela semble fonctionner pour moi. Je n'ai pas encore eu de problèmes avec ça ...

CAST('1/1/' + CAST(YEAR(GETDATE()) AS VARCHAR(30)) AS DATETIME) + (DATEPART(wk, YOUR_DATE) * 7 - 7) as 'FirstDayOfWeek'
CAST('1/1/' + CAST(YEAR(GETDATE()) AS VARCHAR(30)) AS DATETIME) + (DATEPART(wk, YOUR_DATE) * 7) as 'LastDayOfWeek'

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

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


Puisque la date de Julian 0 est un lundi il suffit d'ajouter le nombre de semaines au dimanche qui est le jour précédent -1 Ex. sélectionnez dateadd (wk, datediff (wk, 0, getdate ()), - 1)


Pour répondre pourquoi vous obtenez un lundi et pas un dimanche:

Vous ajoutez un nombre de semaines à la date 0. Qu'est-ce que la date 0? 1900-01-01. Quel était le jour le 1900-01-01? Lundi. Donc, dans votre code, vous dites: combien de semaines se sont écoulées depuis le lundi 1er janvier 1900? Appelons ça [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 jours de plus, ... tout comme DATEDIFF reconnaît seulement les frontières qui ont été franchies. Par exemple, ces deux retours 1, même si certaines personnes se plaignent qu'il devrait y avoir une logique sensible intégré pour arrondir vers le haut ou vers le bas:

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 casse pas si vous modifiez votre paramètre DATEFIRST (ou si votre code est en cours d'exécution 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 jives, alors vous devriez utiliser une fonction qui dépend du paramètre DATEFIRST , par exemple

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

Donc, si vous modifiez votre paramètre DATEFIRST à lundi, mardi, ce que vous avez, le comportement va changer. Selon le 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

Maintenant, vous avez beaucoup d'alternatives, mais lequel est le plus performant? Je serais surpris s'il y avait des différences majeures mais j'ai rassemblé toutes les réponses fournies jusqu'ici et les ai menées à travers deux séries de tests - un bon marché et un coûteux. J'ai mesuré les statistiques des clients parce que je ne vois pas d'E / S ou de mémoire jouer un rôle dans la performance ici (bien que ceux-ci puissent entrer en jeu selon la façon dont la fonction est utilisée). Dans mes tests, les résultats sont:

Requête d'attribution "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

Requête d'attribution "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 relayer les détails de mes tests si désiré - en m'arrêtant ici car cela devient déjà assez long. J'étais un peu surpris de voir Curt sortir comme le plus rapide du haut de gamme, compte tenu du nombre de calculs et du code en ligne. Peut-être que je vais faire des tests plus approfondis et bloguer à ce sujet ... si vous n'avez aucune objection à ce que je publie vos fonctions ailleurs.


Set DateFirst 1;

Select 
    Datepart(wk, TimeByDay) [Week]
    ,Dateadd(d,
                CASE 
                WHEN  Datepart(dw, TimeByDay) = 1 then 0
                WHEN  Datepart(dw, TimeByDay) = 2 then -1
                WHEN  Datepart(dw, TimeByDay) = 3 then -2
                WHEN  Datepart(dw, TimeByDay) = 4 then -3
                WHEN  Datepart(dw, TimeByDay) = 5 then -4
                WHEN  Datepart(dw, TimeByDay) = 6 then -5
                WHEN  Datepart(dw, TimeByDay) = 7 then -6
                END
                , TimeByDay) as StartOfWeek

from TimeByDay_Tbl

C'est ma logique. Définissez le premier jour de la semaine à lundi puis calculez le jour de la semaine, puis utilisez DateAdd et Case I pour calculer la date du lundi précédent.





date