ultimo - ¿Cómo obtener el último día de la semana pasada en sql?

obtener todos los dias de un mes sql server

Question

¿Cómo obtener la última fecha de la última semana en sql? Me refiero a la última fecha de domingo con la consulta?

Answer 1

Aquí está el código para obtener la fecha del último sábado. Este método es independiente de la configuración de la base de datos.

declare @lastSaturday date,
        @today date,
        @todayName varchar(20);

select  @todayName = datename(weekday, getdate()), @today = getdate();

select  
            case @todayName 
                when ''Saturday'' then @today
                when ''Sunday'' then dateadd(day,-1,@today)
                when ''Monday'' then dateadd(day,-2,@today)
                when ''Tuesday'' then dateadd(day,-3,@today)
                when ''Wednesday'' then dateadd(day,-4,@today)
                when ''Thursday'' then dateadd(day,-5,@today)
                when ''Friday'' then dateadd(day,-6,@today)
            end  as LastSaturday;

Answer 2

Aquí hay un excelente artículo sobre cómo hacer esto:

http://www.objectreference.net/post/SQL-Find-last-week-date-range.aspx

Desearía usar la variable @StartOfPrevWeek.

Answer 3

El SQL es más sencillo con una tabla de calendario adecuada. Sin vudú.

select max(cal_date) end_of_last_week
from calendar
where (cal_date < current_date and day_of_week = ''Sun'');

end_of_last_week
--
2011-05-01

Answer 4

Esto te dará el próximo y precioso viernes de una fecha y hora determinadas

DECLARE @PREVIOUS int, @dtmStart datetime,@dtmEnd datetime, @NEXT int;
SET @dtmStart = ''12/10/2013'';
SET @dtmEnd = ''12/11/2013'';

select @PREVIOUS = datepart(dw,@dtmStart)
 WHILE @PREVIOUS <> 6
BEGIN 
    SET @dtmStart = DATEADD(day , -1 ,@dtmStart)
    SET @PREVIOUS = datepart(dw,@dtmStart)
  CONTINUE 
END 
select @dtmStart

 SELECT @NEXT = DATEPART(dw, @dtmEnd)
   WHILE @NEXT <> 6
BEGIN 
    SET @dtmEnd = DATEADD(day , 1 ,@dtmEnd)
    SET @NEXT = datepart(dw,@dtmEnd)
  CONTINUE 
END 
select @dtmEnd

Answer 5

Independientemente de la configuración DATEFIRST real, el último domingo podría encontrarse así:

SELECT DATEADD(day,
               -1 - (DATEPART(weekday, GETDATE()) + @@DATEFIRST - 2) % 7,
               GETDATE()
              ) AS LastSunday

Reemplace GETDATE() con un parámetro @date para obtener el último domingo antes de una fecha en particular.

Answer 6

Para obtener el domingo anterior, o hoy si hoy es domingo, intente esto

DATEADD(day,- (DATEPART(dw,getdate()) + @@DATEFIRST -1) % 7, getdate())

Answer 7

El domingo pasado ( que es el final de "la semana pasada" )

SELECT DATEADD(wk, DATEDIFF(wk, 6, CURRENT_TIMESTAMP), 6) AS LAST_SUNDAY

Esta semana ( Asumiendo el formato de semana de lunes a dom )

SELECT DATEADD(wk, DATEDIFF(wk, 7, CURRENT_TIMESTAMP), 7) AS START_OF_WEEK
SELECT DATEADD(wk, DATEDIFF(wk, 6, CURRENT_TIMESTAMP), 6 + 7) AS END_OF_WEEK

Resultados

START_OF_WEEK
-----------------------
2011-05-02 00:00:00.000

END_OF_WEEK
-----------------------
2011-05-08 00:00:00.000

Ejemplos para explicar el vudú ( Use esto para cambiar el SQL anterior al inicio de semana que desee y al día de la semana que finaliza )

• Los ejemplos a continuación ubican los días de la semana dentro de la semana actual (de domingo a sábado)
• Si el actual END_OF_WEEK es la próxima semana de Sun-Sat, entonces necesitas +7 al valor de esta semana. (Consulte el ejemplo END_OF_WEEK anterior).

SQL a continuación

SELECT DATEADD(wk, DATEDIFF(wk, -2, CURRENT_TIMESTAMP), -2) AS DAY_OF_WEEK /* Saturday */
SELECT DATEADD(wk, DATEDIFF(wk, -1, CURRENT_TIMESTAMP), -1) AS DAY_OF_WEEK /* Sunday */
SELECT DATEADD(wk, DATEDIFF(wk, 0, CURRENT_TIMESTAMP), 0) AS DAY_OF_WEEK /* Monday */
SELECT DATEADD(wk, DATEDIFF(wk, 1, CURRENT_TIMESTAMP), 1) AS DAY_OF_WEEK /* Tuesday */
SELECT DATEADD(wk, DATEDIFF(wk, 2, CURRENT_TIMESTAMP), 2) AS DAY_OF_WEEK /* Wednesday */
SELECT DATEADD(wk, DATEDIFF(wk, 3, CURRENT_TIMESTAMP), 3) AS DAY_OF_WEEK /* Thursday */
SELECT DATEADD(wk, DATEDIFF(wk, 4, CURRENT_TIMESTAMP), 4) AS DAY_OF_WEEK /* Friday */
SELECT DATEADD(wk, DATEDIFF(wk, 5, CURRENT_TIMESTAMP), 5) AS DAY_OF_WEEK /* Saturday */
SELECT DATEADD(wk, DATEDIFF(wk, 6, CURRENT_TIMESTAMP), 6) AS DAY_OF_WEEK /* Sunday */
SELECT DATEADD(wk, DATEDIFF(wk, 7, CURRENT_TIMESTAMP), 7) AS DAY_OF_WEEK /* Monday */
SELECT DATEADD(wk, DATEDIFF(wk, 8, CURRENT_TIMESTAMP), 8) AS DAY_OF_WEEK /* Tuesday */
SELECT DATEADD(wk, DATEDIFF(wk, 9, CURRENT_TIMESTAMP), 9) AS DAY_OF_WEEK /* Wednesday */
SELECT DATEADD(wk, DATEDIFF(wk, 10, CURRENT_TIMESTAMP), 10) AS DAY_OF_WEEK /* Thursday */
SELECT DATEADD(wk, DATEDIFF(wk, 11, CURRENT_TIMESTAMP), 11) AS DAY_OF_WEEK /* Friday */
SELECT DATEADD(wk, DATEDIFF(wk, 12, CURRENT_TIMESTAMP), 12) AS DAY_OF_WEEK /* Saturday */
etc...

Answer 8

DECLARE @LastSunday DATETIME 

-- This will get the previous Sunday with time as 23:59:59 
SELECT @LastSunday = Dateadd(SECOND, -1, Dateadd(WK, Datediff(WK, 6, 
                                                     CURRENT_TIMESTAMP) 
                                                , 7)) 

SELECT @LastSunday 

-- This gets the monday prior to it and time of 00:00:00 
SELECT Dateadd(SECOND, 1, Dateadd(DAY, -7, @LastSunday)) 
-- This will make you time spans between eg, Monday 16/07/2012 00:00:00 through to Sunday 22/07/2012 23:59:59
-- Then use them in your WHERE clause like this 
-- SELECT X,Y,Z From SomeTable 
-- WHERE DateField BETWEEN @PreviousMondayToLastSunday AND @LastSunday 

Answer 9

SELECT (DATEADD(DAY, ((DATEPART(dw, @Date) - 1) * -1), @Date))

Answer 10

SET @EndDate = GETDATE()-DatePart(dw, GETDATE());