Obtenha o primeiro dia da semana no SQL Server

Estou tentando agrupar registros por semana, armazenando a data agregada como o primeiro dia da semana. No entanto, a técnica padrão que eu uso para arredondar as datas não parece funcionar corretamente com semanas (embora isso aconteça por dias, meses, anos, trimestres e qualquer outro período de tempo que eu apliquei).

Aqui está o SQL:

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

Isso retorna 2011-08-22 00:00:00.000 , que é uma segunda-feira, não um domingo. Selecionar @@datefirst retorna 7 , que é o código para o domingo, então o servidor está configurado corretamente, tanto quanto eu sei.

Eu posso ignorar isso facilmente alterando o código acima para:

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

Mas o fato de eu ter que fazer tal exceção me deixa um pouco desconfortável. Além disso, desculpe se esta é uma pergunta duplicada. Eu encontrei algumas questões relacionadas, mas nenhuma que abordou este aspecto especificamente.

Para responder por que você está recebendo uma segunda-feira e não um domingo:

Você está adicionando um número de semanas à data 0. O que é a data 0? 1900-01-01. Qual foi o dia de 1900-01-01? Segunda-feira. Então, no seu código, você está dizendo, quantas semanas se passaram desde segunda-feira, 1º de janeiro de 1900? Vamos chamar isso [n]. Ok, agora adicione [n] semanas a segunda-feira, 1 de janeiro de 1900. Você não deve se surpreender que isso acabe sendo uma segunda-feira. DATEADD não tem idéia de que você deseja adicionar semanas, mas somente até chegar a um domingo, é só adicionar 7 dias, depois adicionar mais 7 dias, … assim como DATEDIFF reconhece apenas os limites que foram cruzados. Por exemplo, ambos retornam 1, embora algumas pessoas queixem-se de que deveria haver alguma lógica sensata incorporada para arredondar para cima ou para baixo:

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

Para responder como conseguir um domingo:

Se você quiser um domingo, escolha uma data base que não seja uma segunda-feira, mas sim um domingo. Por exemplo:

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

Isso não será interrompido se você alterar a configuração DATEFIRST (ou se seu código estiver sendo executado para um usuário com uma configuração diferente) – desde que você ainda deseje um domingo, independentemente da configuração atual. Se você quiser essas duas respostas, então você deve usar uma function que depende da configuração DATEFIRST , por exemplo

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

Então, se você alterar sua configuração DATEFIRST para segunda-feira, terça-feira, o que acontecerá, o comportamento será alterado. Dependendo do comportamento desejado, você pode usar uma destas funções:

 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 

Agora, você tem muitas alternativas, mas qual delas tem melhor desempenho? Eu ficaria surpreso se houvesse grandes diferenças, mas eu coletei todas as respostas fornecidas até agora e passei por dois conjuntos de testes – um barato e outro caro. Eu medi as statistics do cliente porque não vejo a E / S ou a memory desempenhando um papel no desempenho aqui (embora elas possam entrar em jogo dependendo de como a function é usada). Nos meus testes, os resultados são:

Consulta de atribuição “barata”:

 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 

Consulta de atribuição “cara”:

 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 

Eu posso retransmitir os detalhes dos meus testes, se desejar – parando aqui, pois isso já está ficando bastante lento. Fiquei um pouco surpreso ao ver que o Curt é o mais rápido no topo, dado o número de cálculos e o código embutido. Talvez eu faça alguns testes mais completos e blogue sobre isso … se vocês não tiverem objeções em publicar suas funções em outro lugar.

Para estes que precisam obter:

Segunda-feira = 1 e domingo = 7:

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

Domingo = 1 e sábado = 7:

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

Acima havia um exemplo semelhante, mas graças ao duplo “% 7” seria muito mais lento.

Isso funciona maravilhosamente para mim:

 CREATE FUNCTION [dbo]. [StartOfWeek]
 (
   @INTERNET DATETIME
 )
 DEVOLUÇÃO DE DATA

 COMO
 INÍCIO
   - Isso não funciona em function.
   - SET DATEFIRST 1 - defina a segunda-feira como o primeiro dia da semana.

   DECLARAR @DOW INT - para armazenar o dia da semana
   SET @INPUTDATE = CONVERT (VARCHAR (10), @INPUTDATE, 111)
   SET @DOW = DATEPART (DW, @INPUTDATE)

   - Conversão mágica de segunda a 1, terça a 2, etc.
   - Independente do que o SQL Server pensa sobre o início da semana.
   - Mas aqui temos o domingo marcado como 0, mas vamos consertar isso depois.
   SET @DOW = (@DOW + @@ DATEFIRST - 1)% 7
   SE @DOW = 0 SET @DOW = 7 - correção para domingo

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

 FIM

Pesquisei este script no Google:

 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

Talvez você precise disso:

 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) 

Função

 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
 (
     @currentDate date
 )
 RETORNA INT
 COMO
 INÍCIO
     - obter configuração DATEFIRST
     DECLARAR @ds int = @@ DATEFIRST 
     - obter o número do dia da semana na configuração atual DATEFIRST
     DECLARAR @dow int = DATEPART (dw, @ currentDate) 

     DECLARAR @wd int = 1 + (((@dow + @ds)% 7) +5)% 7 - isto é sempre retornar seg como 1, ter como 2 ... dom como 7 

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

 FIM

Para aqueles que precisam da resposta no trabalho e a criação da function é proibida pelo seu DBA, a seguinte solução funcionará:

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

Isso dá o início dessa semana. Aqui eu suponho que os domingos são o começo das semanas. Se você acha que segunda-feira é o começo, você deve usar:

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

Uma vez que a data juliana 0 é uma segunda-feira, basta adicionar o número de semanas a domingo, que é o dia anterior a -1, por exemplo. selecione 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 

Essa é minha lógica. Defina o primeiro dia da semana como segunda-feira e calcule qual é o dia da semana em que um dia é dado. Depois, use DateAdd e Case I para calcular a data que teria sido na segunda-feira anterior daquela semana.

Eu não tenho nenhum problema com qualquer uma das respostas dadas aqui, mas eu acho que o meu é muito mais simples de implementar e entender. Eu não executei nenhum teste de desempenho, mas deve ser neglegível.

Então eu deduzi a minha resposta do fato de que as datas são armazenadas no servidor SQL como números inteiros, (eu estou falando apenas sobre o componente de data). Se você não acredita em mim, tente este SELECT CONVERT (INT, GETDATE ()) e vice-versa.

Agora, sabendo disso, você pode fazer algumas equações matemáticas legais. Você pode ser capaz de criar um melhor, mas aqui está o meu.

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

Eu tive um problema parecido. Dado um encontro, eu queria pegar a data da segunda-feira daquela semana.

Eu usei a seguinte lógica: Encontre o número do dia na semana no intervalo de 0 a 6 e, em seguida, subtraia esse valor da data de origem.

Eu usei: DATEADD (dia, – (DATEPART (dia da semana,) + 5)% 7,)

Como DATEPRRT (dia da semana,) retorna 1 = domingo … 7 = sábado, DATEPART (dia da semana) + 5)% 7 retorna 0 = segunda-feira … 6 = domingo.

Subtrair esse número de dias da data original indica a segunda-feira anterior. A mesma técnica pode ser usada para qualquer dia inicial da semana.

Eu achei isso simples e útil. Funciona mesmo se o primeiro dia da semana for domingo ou segunda-feira.

DECLARAR @BaseDate AS Date

SET @BaseDate = GETDATE ()

DECLARAR @FisrtDOW AS Date

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

Talvez eu esteja simplificando aqui, e esse pode ser o caso, mas isso parece funcionar para mim. Ainda não tive problemas com isso …

 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'