Ottieni il primo giorno della settimana in SQL Server

Sto provando a raggruppare i record per settimana, memorizzando la data aggregata come il primo giorno della settimana. Tuttavia, la tecnica standard che utilizzo per le date di arrotondamento non sembra funzionare correttamente con settimane (anche se lo fa per giorni, mesi, anni, trimestri e qualsiasi altro periodo di tempo in cui l’ho applicato).

Ecco l’SQL:

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

Questo restituisce 2011-08-22 00:00:00.000 , che è un lunedì, non una domenica. Selezionando @@datefirst restituisce 7 , che è il codice per domenica, quindi il server è configurato correttamente per quanto ne so.

Posso aggirare questo facilmente abbastanza cambiando il codice sopra a:

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

Ma il fatto che devo fare un’eccezione mi rende un po ‘a disagio. Inoltre, mi scuso se si tratta di una domanda duplicata. Ho trovato alcune domande correlate ma nessuna che riguardasse specificamente questo aspetto.

Per rispondere perché stai ricevendo un lunedì e non una domenica:

Stai aggiungendo un numero di settimane alla data 0. Che cos’è la data 0? 1900-01-01. Qual è stato il giorno 1900-01-01? Lunedi. Quindi nel tuo codice stai dicendo, quante settimane sono trascorse da lunedì 1 gennaio 1900? Chiamiamolo [n]. Ok, ora aggiungi [n] settimane a lunedì 1 gennaio 1900. Non dovresti essere sorpreso che questo finisca per essere un lunedì. DATEADD non ha idea che tu voglia aggiungere settimane ma solo fino a domenica, aggiungendo solo 7 giorni, aggiungendo altri 7 giorni … proprio come DATEDIFF riconosce solo i confini che sono stati superati. Ad esempio, entrambi restituiscono 1, anche se alcune persone si lamentano del fatto che dovrebbe esserci una logica ragionevole integrata per arrotondare o ridurre:

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

Per rispondere a come ottenere una domenica:

Se vuoi una domenica, scegli una data base che non è un lunedì ma piuttosto una domenica. Per esempio:

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

Questo non si interromperà se modifichi l’impostazione DATEFIRST (o il tuo codice è in esecuzione per un utente con un’impostazione diversa) – a condizione che desideri comunque una domenica indipendentemente dall’impostazione corrente. Se vuoi che quelle due risposte siano jive, allora dovresti usare una funzione che dipende dall’impostazione di DATEFIRST , ad es

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

Pertanto, se cambi l’impostazione DATEFIRST a lunedì, martedì, cosa cambierai, il comportamento cambierà. A seconda del comportamento che desideri, puoi utilizzare una di queste funzioni:

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

…o…

 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 

Ora, hai un sacco di alternative, ma quale si comporta meglio? Sarei sorpreso se ci fossero grosse differenze, ma ho raccolto tutte le risposte fornite finora e le ho sottoposte a due serie di test: uno economico e uno costoso. Ho misurato le statistiche dei clienti perché non vedo l’I / O o la memoria che riproducono una parte nella performance qui (anche se potrebbero entrare in gioco a seconda di come viene utilizzata la funzione). Nei miei test i risultati sono:

Query di assegnazione “economica”:

 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 

Query di assegnazione “costosa”:

 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 

Posso inoltrare i dettagli dei miei test se lo desideri, fermandoti qui perché questo sta già diventando piuttosto prolisso. Sono stato un po ‘sorpreso nel vedere Curt uscito come il più veloce nella fascia alta, dato il numero di calcoli e il codice inline. Forse farò dei test più approfonditi e ne parlerò a proposito … se voi ragazzi non avete alcuna obiezione a pubblicare le vostre funzioni altrove.

Per questi che hanno bisogno di ottenere:

Lunedì = 1 e domenica = 7:

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

Domenica = 1 e sabato = 7:

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

Sopra c’era un esempio simile, ma grazie al doppio “% 7” sarebbe molto più lento.

Questo funziona meravigliosamente per me:

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

 COME
 INIZIO
   - QUESTO non funziona in funzione.
   - SET DATEFIRST 1 - imposta lunedì come primo giorno della settimana.

   DICHIARARE @DOW INT - per memorizzare il giorno della settimana
   SET @INPUTDATE = CONVERT (VARCHAR (10), @INPUTDATE, 111)
   SET @DOW = DATEPART (DW, @INPUTDATE)

   - Conversione magica di lunedì a 1, martedì a 2, ecc.
   - non importa cosa pensa il server SQL dell'inizio della settimana.
   - Ma qui abbiamo la domenica contrassegnata come 0, ma la ripariamo più tardi.
   SET @DOW = (@DOW + @@ DATEFIRST - 1)% 7
   IF @DOW = 0 SET @DOW = 7 - correzione per domenica

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

 FINE

Googled questo 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

Forse hai bisogno di questo:

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

O

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

Funzione

 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 
 CREATE FUNCTION dbo.fnFirstWorkingDayOfTheWeek
 (
     data @currentDate
 )
 TORNA INT
 COME
 INIZIO
     - Ottieni l'impostazione DATEFIRST
     DECLARE @ds int = @@ DATEFIRST 
     - ottenere il numero del giorno della settimana con l'impostazione DATEFIRST corrente
     DICHIARARE @dow int = DATEPART (dw, @ currentDate) 

     DECLARE @wd int = 1 + (((@ dow + @ ds)% 7) +5)% 7 - questo è sempre return Mon as 1, Tue as 2 ... Sun as 7 

     RETURN DATEADD (dd, 1- @ wd, @ currentDate) 

 FINE

Per chi ha bisogno della risposta sul posto di lavoro e la creazione della funzione è proibita dal proprio DBA, la seguente soluzione funzionerà:

 select *, cast(DATEADD(day, -1*(DATEPART(WEEKDAY, YouDate)-1), YourDate) as DATE) as WeekStart From..... 

Questo dà l’inizio di quella settimana. Qui presumo che le domeniche siano l’inizio delle settimane. Se pensi che il lunedì sia l’inizio, dovresti usare:

 select *, cast(DATEADD(day, -1*(DATEPART(WEEKDAY, YouDate)-2), YourDate) as DATE) as WeekStart From..... 

Dal momento che Julian data 0 è un lunedì aggiungere solo il numero di settimane a domenica che è il giorno prima -1-Eg. seleziona dateadd (wk, datediff (wk, 0, getdate ()), – 1)

 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 

Questa è la mia logica. Imposta il primo della settimana in modo da essere lunedì, quindi calcola qual è il giorno della settimana in cui viene assegnato un giorno, quindi utilizza DateAdd e Case I per calcolare quale sarebbe stata la data del lunedì precedente di quella settimana.

Non ho alcun problema con nessuna delle risposte date qui, tuttavia penso che il mio sia molto più semplice da implementare e capire. Non ho eseguito test delle prestazioni su di esso, ma dovrebbe essere neglegabile.

Quindi ho ricavato la mia risposta dal fatto che le date sono memorizzate nel server SQL come numeri interi (sto parlando solo del componente data). Se non mi credi, prova questo SELECT CONVERT (INT, GETDATE ()) e viceversa.

Ora sapendo questo, puoi fare delle fantastiche equazioni matematiche. Potresti riuscire a inventarne uno migliore, ma qui è mio.

 /* 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, US 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)) 

Ho avuto un problema simile. Data una data, volevo avere la data del lunedì di quella settimana.

Ho usato la seguente logica: trovare il numero del giorno nella settimana nell’intervallo 0-6, quindi sottrarlo dalla data di origine.

Ho utilizzato: DATEADD (giorno, – (DATEPART (giorno della settimana,) + 5)% 7,)

Poiché DATEPRRT (giorno della settimana,) restituisce 1 = domenicae … 7 = sabato, DATEPART (giorno della settimana,) + 5)% 7 restituisce 0 = lunedì … 6 = domenica.

Sottraendo questo numero di giorni dalla data originale si ottiene il lunedì precedente. La stessa tecnica potrebbe essere utilizzata per qualsiasi giorno iniziale della settimana.

Ho trovato questo semplice e utile. Funziona anche se il primo giorno della settimana è domenica o lunedì.

DICHIARARE data @BaseDate AS

SET @BaseDate = GETDATE ()

DICHIARARE @FisrtDOW AS Date

SELECT @FirstDOW = DATEADD (d, DATEPART (WEEKDAY, @ BaseDate) * -1 + 1, @BaseDate)

Forse sto finendo di semplificare qui, e potrebbe essere il caso, ma questo sembra funzionare per me. Non ho ancora avuto problemi con questo …

 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'