85 votos

Consigue el primer día de la semana en Sql Server

Estoy tratando de agrupar los 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 sí lo hace con los días, meses, años, trimestres y cualquier otro marco de tiempo al que lo haya aplicado).

Aquí está el SQL:

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

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

Puedo evitar 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 tenga que hacer una excepción así me hace sentir un poco incómodo. Además, disculpe si esta es una pregunta duplicada. Encontré algunas preguntas relacionadas pero ninguna que tratara este aspecto específicamente.

131voto

Aaron Bertrand Puntos 116343

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

Estás añadiendo un número de semanas a la fecha 0. ¿Qué es la fecha 0? 1900-01-01. ¿Qué día fue el 1900-01-01? El lunes. Así que en tu código dices, ¿cuántas semanas han pasado desde el lunes 1 de enero de 1900? Llamémosle a eso [n]. Ok, ahora agrega [n] semanas al lunes 1 de enero de 1900. No debería sorprenderte que esto termine siendo un lunes. DATEADD no tiene ni idea de que quieres añadir semanas, pero sólo hasta que llegas a un domingo, es sólo añadir 7 días, luego añadir 7 días más, ... al igual que DATEDIFF sólo reconoce los límites que han sido cruzados. Por ejemplo, ambos regresan 1, aunque algunos se quejan de que debería haber alguna lógica sensata incorporada 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 cómo conseguir un domingo:

Si quieres un domingo, entonces escoge 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 cambias tu DATEFIRST (o su código se está ejecutando para un usuario con un ajuste diferente) - siempre y cuando usted todavía quiera un domingo independientemente del ajuste actual. Si quieres esas dos respuestas a jive, entonces deberías usar una función que hace dependen de la DATEFIRST el ajuste, por ejemplo.

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

Así que si cambias tu DATEFIRST fijando el lunes, martes, lo que sea, el comportamiento cambiará. Dependiendo del comportamiento que quieras, podrías 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 es la que mejor funciona? Me sorprendería si hubiera grandes diferencias, pero recogí todas las respuestas proporcionadas hasta ahora y las pasé por dos series de pruebas, una barata y otra cara. Medí las estadísticas de los clientes porque no veo que la E/S o la memoria jueguen un papel en el rendimiento aquí (aunque pueden entrar en juego dependiendo de cómo se utilice 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 "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

Puedo transmitir los detalles de mis pruebas si lo desea, parando aquí ya que esto ya se está alargando bastante. Me sorprendió un poco ver que Curt salió como el más rápido en la parte alta, dado el número de cálculos y el código en línea. Tal vez haga algunas pruebas más exhaustivas y escriba un blog sobre ello... si no tienen ninguna objeción a que publique sus funciones en otro lugar.

4voto

trailmax Puntos 5164

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

3voto

Curt Puntos 42871

Busca en Google esto 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

2voto

Gandarez Puntos 557

Tal vez 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

2voto

JG JIN Puntos 21
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

Iteramos.com

Iteramos es una comunidad de desarrolladores que busca expandir el conocimiento de la programación mas allá del inglés.
Tenemos una gran cantidad de contenido, y también puedes hacer tus propias preguntas o resolver las de los demás.

Powered by:

X