sql-server - obtener - primer dia de la semana sql server

Obtenga el primer día de la semana en SQL Server

Question

Intento agrupar registros por semana, almacenando la fecha agregada como el primer día de la semana. Sin embargo, la técnica estándar que utilizo para redondear las fechas no parece funcionar correctamente con las semanas (aunque lo hace por días, meses, años, trimestres y cualquier otro período de tiempo al que lo haya aplicado).

Aquí está el SQL:

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

Esto devuelve 2011-08-22 00:00:00.000 , que es un lunes, no un domingo. Seleccionar @@datefirst devuelve 7 , que es el código para el domingo, por lo que el servidor está configurado correctamente, por lo que sé.

Puedo omitir esto fácilmente cambiando el código anterior a:

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

Pero el hecho de que tengo que hacer una excepción me hace sentir un poco incómodo. Además, me disculpo si esta es una pregunta duplicada. Encontré algunas preguntas relacionadas, pero ninguna que aborda este aspecto específicamente.

Answer 1

Buscado en Google esta secuencia de comandos:

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

Answer 2

Como la fecha juliana 0 es un lunes, solo agregue el número de semanas al domingo, que es el día anterior a -1 Ej. seleccione dateadd (wk, datediff (wk, 0, getdate ()), - 1)

Answer 3

Encontré esto simple y útil. Funciona incluso si el primer día de la semana es domingo o lunes.

DECLARE @BaseDate AS Fecha

SET @BaseDate = GETDATE ()

DECLARAR @FisrtDOW AS Fecha

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

Answer 4

Esto funciona maravillosamente para mí:

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 5

No tengo ningún problema con ninguna de las respuestas dadas aquí, sin embargo, creo que la mía es mucho más sencilla de implementar y comprender. No he realizado ninguna prueba de rendimiento, pero debería ser negligente.

Así que obtuve mi respuesta del hecho de que las fechas se almacenan en el servidor SQL como enteros (estoy hablando solo del componente de fecha). Si no me crees, prueba con SELECT CONVERT (INT, GETDATE ()) y viceversa.

Ahora sabiendo esto, puedes hacer algunas ecuaciones matemáticas geniales. Es posible que puedas encontrar uno mejor, pero aquí está el mío.

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

Answer 6

Para aquellos que necesitan la respuesta en el trabajo y la creación de funciones está prohibida por su DBA, la siguiente solución funcionará:

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

Esto da el comienzo de esa semana. Aquí supongo que los domingos son el comienzo de las semanas. Si crees que el lunes es el comienzo, debes usar:

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

Answer 7

Para estos que necesitan obtener:

Lunes = 1 y Domingo = 7:

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

Domingo = 1 y sábado = 7:

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

Arriba había un ejemplo similar, pero gracias al doble "% 7" sería mucho más lento.

Answer 8

Quizás necesites esto:

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)

Función

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 9

Tal vez estoy simplificando demasiado aquí, y ese puede ser el caso, pero esto parece funcionar para mí. No he tenido ningún problema con eso todaví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''

Answer 10

Tuve un problema similar. Dada una fecha, quería obtener la fecha del lunes de esa semana.

Usé la siguiente lógica: encuentre el número del día en la semana en el rango de 0-6, luego reste eso de la fecha original.

Utilicé: DATEADD (día, - (DATEPART (día de la semana,) + 5)% 7,)

Dado que DATEPRRT (día de la semana), devuelve 1 = Sundaye ... 7 = Saturday, DATEPART (día de la semana,) + 5)% 7 devuelve 0 = lunes ... 6 = domingo.

Restando este número de días de la fecha original da el lunes anterior. La misma técnica podría usarse para cualquier día de inicio de la semana.

Answer 11

Para responder por qué recibes un lunes y no un domingo:

Está agregando un número de semanas a la fecha 0. ¿Qué es la fecha 0? 1900-01-01. ¿Cuál fue el día en 1900-01-01? Lunes. Entonces, en su código, ¿cuántas semanas han pasado desde el lunes 1 de enero de 1900? Llamemos a eso [n]. De acuerdo, ahora agregue [n] semanas al lunes, 1 de enero de 1900. No debería sorprenderse que esto termine siendo un lunes. DATEADD no tiene idea de que quiera agregar semanas, pero solo hasta que llegue a un domingo, solo agregue 7 días, luego agregue 7 días más, ... al igual que DATEDIFF solo reconoce los límites que se han cruzado. Por ejemplo, ambos devuelven 1, aunque algunos se quejan de que debe haber alguna lógica lógica integrada para redondear hacia arriba o hacia abajo:

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

Para responder a cómo obtener un domingo:

Si quieres un domingo, elige una fecha base que no sea un lunes sino un domingo. Por ejemplo:

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

Esto no se romperá si cambia su configuración DATEFIRST (o su código se está ejecutando para un usuario con una configuración diferente), siempre que desee un domingo independientemente de la configuración actual. Si quiere esas dos respuestas para jive, entonces debe usar una función que dependa de la configuración DATEFIRST , por ej.

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

Entonces, si cambias tu configuración DATEFIRST al lunes, martes, ¿qué tienes ?, el comportamiento cambiará. Según el comportamiento que desee, puede usar una de estas funciones:

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

Ahora, tienes muchas alternativas, pero ¿cuál funciona mejor? Me sorprendería si hubiera diferencias importantes, pero recolecté todas las respuestas proporcionadas hasta el momento y las realicé en dos series de pruebas, una barata y otra cara. Medí las estadísticas del cliente porque no veo que la E / S o la memoria desempeñen un papel en el rendimiento aquí (aunque pueden entrar en juego dependiendo de cómo se usa la función). En mis pruebas, los resultados son:

Consulta de asignación "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 asignación "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

Puedo transmitir los detalles de mis pruebas si lo desea, deteniéndome aquí, ya que esto ya se está volviendo bastante largo. Me sorprendió un poco ver que Curt aparecía como el más rápido en el extremo superior, dada la cantidad de cálculos y el código en línea. Tal vez haga algunas pruebas más exhaustivas y bloguee sobre ello ... si ustedes no tienen ninguna objeción al publicar sus funciones en otro lado.

Answer 12

CREATE FUNCTION dbo.fnFirstWorkingDayOfTheWeek ( @currentDate date ) RETURNS INT AS BEGIN -- get DATEFIRST setting DECLARE @ds int = @@DATEFIRST -- get week day number under current DATEFIRST setting DECLARE @dow int = DATEPART(dw,@currentDate) DECLARE @wd int = 1+(((@dow+@ds) % 7)+5) % 7 -- this is always return Mon as 1,Tue as 2 ... Sun as 7 RETURN DATEADD(dd,1-@wd,@currentDate) END

Answer 13

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

Esta es mi lógica Establezca que el primero de la semana sea el lunes, luego calcule cuál es el día de la semana en que se entrega el día, y luego usando DateAdd y Case I calcule cuál habría sido la fecha del lunes anterior de esa semana.