the - sql server week number of month

¿Cómo se obtiene la "fecha de inicio de la semana" y la "fecha de fin de semana" del número de semana en SQL Server?

Question

Tengo una consulta que cuenta las fechas de boda de los miembros en la base de datos ...

Select
Sum(NumberOfBrides) As [Wedding Count],
DATEPART( wk, WeddingDate) as [Week Number],
DATEPART( year, WeddingDate) as [Year]
FROM MemberWeddingDates
Group By DATEPART( year, WeddingDate), DATEPART( wk, WeddingDate)
Order By Sum(NumberOfBrides) Desc

¿Cómo hago ejercicio cuando el comienzo y el final de cada semana se representan en el conjunto de resultados?

Select
Sum(NumberOfBrides) As [Wedding Count],
DATEPART( wk, WeddingDate) as [Week Number],
DATEPART( year, WeddingDate) as [Year],
??? as WeekStart,
??? as WeekEnd
FROM MemberWeddingDates
Group By DATEPART( year, WeddingDate), DATEPART( wk, WeddingDate)
Order By Sum(NumberOfBrides) Desc

Answer 1

A continuación, la consulta dará datos entre el inicio y el final de la semana actual, comenzando de domingo a sábado

SELECT DOB FROM PROFILE_INFO WHERE DAY(DOB) BETWEEN
DAY( CURRENT_DATE() - (SELECT DAYOFWEEK(CURRENT_DATE())-1))
AND
DAY((CURRENT_DATE()+(7 - (SELECT DAYOFWEEK(CURRENT_DATE())) ) ))
AND
MONTH(DOB)=MONTH(CURRENT_DATE())

Answer 2

Acabo de encontrar un caso similar con este, pero la solución aquí parece no ayudarme. Así que trato de resolverlo por mi cuenta. Solo resuelvo la fecha de inicio de la semana, la fecha de finalización de la semana debe ser de lógica similar.

Select
Sum(NumberOfBrides) As [Wedding Count],
DATEPART( wk, WeddingDate) as [Week Number],
DATEPART( year, WeddingDate) as [Year],
DATEADD(DAY, 1 - DATEPART(WEEKDAY, dateadd(wk, DATEPART( wk, WeddingDate)-1, DATEADD(yy,DATEPART( year, WeddingDate)-1900,0))), dateadd(wk, DATEPART( wk, WeddingDate)-1, DATEADD(yy,DATEPART( year, WeddingDate)-1900,0))) as [Week Start]
FROM MemberWeddingDates
Group By DATEPART( year, WeddingDate), DATEPART( wk, WeddingDate)
Order By Sum(NumberOfBrides) Desc

Answer 3

Ampliando @Tomalak''s respuesta de @Tomalak''s . La fórmula funciona durante días que no sean el domingo y el lunes, pero debe usar valores diferentes para el lugar donde está el 5. Una forma de llegar al valor que necesita es

Value Needed = 7 - (Value From Date First Documentation for Desired Day Of Week) - 1

aquí hay un enlace al documento: https://msdn.microsoft.com/en-us/library/ms181598.aspx

Y aquí hay una tabla que lo explica.

 | DATEFIRST VALUE | Formula Value | 7 - DATEFIRSTVALUE - 1
Monday | 1 | 5 | 7 - 1- 1 = 5
Tuesday | 2 | 4 | 7 - 2 - 1 = 4
Wednesday | 3 | 3 | 7 - 3 - 1 = 3
Thursday | 4 | 2 | 7 - 4 - 1 = 2
Friday | 5 | 1 | 7 - 5 - 1 = 1
Saturday | 6 | 0 | 7 - 6 - 1 = 0
Sunday | 7 | -1 | 7 - 7 - 1 = -1

Pero no tiene que recordar esa tabla y solo la fórmula, y en realidad podría usar una ligeramente diferente también, la principal necesidad es usar un valor que hará que el resto sea la cantidad correcta de días.

Aquí hay un ejemplo de trabajo:

DECLARE @MondayDateFirstValue INT = 1
DECLARE @FridayDateFirstValue INT = 5
DECLARE @TestDate DATE = GETDATE()
SET @MondayDateFirstValue = 7 - @MondayDateFirstValue - 1
SET @FridayDateFirstValue = 7 - @FridayDateFirstValue - 1
SET DATEFIRST 6 -- notice this is saturday
SELECT
DATEADD(DAY, 0 - (@@DATEFIRST + @MondayDateFirstValue + DATEPART(dw,@TestDate)) % 7, @TestDate) as MondayStartOfWeek
,DATEADD(DAY, 6 - (@@DATEFIRST + @MondayDateFirstValue + DATEPART(dw,@TestDate)) % 7, @TestDate) as MondayEndOfWeek
,DATEADD(DAY, 0 - (@@DATEFIRST + @FridayDateFirstValue + DATEPART(dw,@TestDate)) % 7, @TestDate) as FridayStartOfWeek
,DATEADD(DAY, 6 - (@@DATEFIRST + @FridayDateFirstValue + DATEPART(dw,@TestDate)) % 7, @TestDate) as FridayEndOfWeek
SET DATEFIRST 2 --notice this is tuesday
SELECT
DATEADD(DAY, 0 - (@@DATEFIRST + @MondayDateFirstValue + DATEPART(dw,@TestDate)) % 7, @TestDate) as MondayStartOfWeek
,DATEADD(DAY, 6 - (@@DATEFIRST + @MondayDateFirstValue + DATEPART(dw,@TestDate)) % 7, @TestDate) as MondayEndOfWeek
,DATEADD(DAY, 0 - (@@DATEFIRST + @FridayDateFirstValue + DATEPART(dw,@TestDate)) % 7, @TestDate) as FridayStartOfWeek
,DATEADD(DAY, 6 - (@@DATEFIRST + @FridayDateFirstValue + DATEPART(dw,@TestDate)) % 7, @TestDate) as FridayEndOfWeek

Este método sería DATEFIRST configuración DATEFIRST que es lo que necesitaba, ya que estoy construyendo una dimensión de fecha con varios métodos de semana incluidos.

Answer 4

Aquí hay otra versión. Si su Escenario requiere que el sábado sea el primer día de la semana y el viernes sea el último día de la semana, el siguiente código manejará eso:

 DECLARE @myDate DATE = GETDATE()
SELECT @myDate,
DATENAME(WEEKDAY,@myDate),
DATEADD(DD,-(CHOOSE(DATEPART(dw, @myDate), 1,2,3,4,5,6,0)),@myDate) AS WeekStartDate,
DATEADD(DD,7-CHOOSE(DATEPART(dw, @myDate), 2,3,4,5,6,7,1),@myDate) AS WeekEndDate

Answer 5

Aquí hay una solución agnóstica DATEFIRST :

SET DATEFIRST 4 /* or use any other weird value to test it */
DECLARE @d DATETIME
SET @d = GETDATE()
SELECT
@d ThatDate,
DATEADD(dd, (@@DATEFIRST + 5 + DATEPART(dw, @d)) % 7, @d) Monday,
DATEADD(dd, 6 - (@@DATEFIRST + 5 + DATEPART(dw, @d)) % 7, @d) Sunday

Answer 6

La respuesta más votada funciona bien, excepto en la primera semana y la última semana de un año. Por ejemplo, si el valor de WeddingDate es ''2016-01-01'', el resultado será 2015-12-27 y 2016-01-02 , pero la respuesta correcta es 2016-01-01 y 2016-01-02 .

Prueba esto:

Select
Sum(NumberOfBrides) As [Wedding Count],
DATEPART( wk, WeddingDate) as [Week Number],
DATEPART( year, WeddingDate) as [Year],
MAX(CASE WHEN DATEPART(WEEK, WeddingDate) = 1 THEN CAST(DATEADD(YEAR, DATEDIFF(YEAR, 0, WeddingDate), 0) AS date) ELSE DATEADD(DAY, 7 * DATEPART(WEEK, WeddingDate), DATEADD(DAY, -(DATEPART(WEEKDAY, DATEADD(YEAR, DATEDIFF(YEAR, 0, WeddingDate), 0)) + 6), DATEADD(YEAR, DATEDIFF(YEAR, 0, WeddingDate), 0))) END) as WeekStart,
MAX(CASE WHEN DATEPART(WEEK, WeddingDate) = DATEPART(WEEK, DATEADD(DAY, -1, DATEADD(YEAR, DATEDIFF(YEAR, 0, WeddingDate) + 1, 0))) THEN DATEADD(DAY, -1, DATEADD(YEAR, DATEDIFF(YEAR, 0, WeddingDate) + 1, 0)) ELSE DATEADD(DAY, 7 * DATEPART(WEEK, WeddingDate) + 6, DATEADD(DAY, -(DATEPART(WEEKDAY, DATEADD(YEAR, DATEDIFF(YEAR, 0, WeddingDate), 0)) + 6), DATEADD(YEAR, DATEDIFF(YEAR, 0, WeddingDate), 0))) END) as WeekEnd
FROM MemberWeddingDates
Group By DATEPART( year, WeddingDate), DATEPART( wk, WeddingDate)
Order By Sum(NumberOfBrides) Desc;

El resultado se ve así:

Funciona durante todas las semanas, primero u otros.

Answer 7

No estoy seguro de lo útil que es esto, pero terminé aquí buscando una solución en Netezza SQL y no pude encontrar uno en el desbordamiento de la pila.

Para IBM Netezza usaría algo (para la semana start mon, week end sun) como:

seleccione next_day (WeddingDate, ''SUN'') -6 como WeekStart,

next_day (WeddingDate, ''SUN'') como WeekEnd

Answer 8

Puede encontrar el día de la semana y agregar una fecha en días para obtener las fechas de inicio y finalización.

DATEADD(dd, -(DATEPART(dw, WeddingDate)-1), WeddingDate) [WeekStart]
DATEADD(dd, 7-(DATEPART(dw, WeddingDate)), WeddingDate) [WeekEnd]

Sin embargo, probablemente también quieras quitarle el tiempo de la fecha.

Answer 9

Vamos a dividir el problema en dos partes:

1) Determinar el día de la semana

El DATEPART(dw, ...) devuelve un número, 1 ... 7, relativo a la configuración DATEFIRST ( docs ). La siguiente tabla resume los valores posibles:

 @@DATEFIRST
+------------------------------------+-----+-----+-----+-----+-----+-----+-----+-----+
| | 1 | 2 | 3 | 4 | 5 | 6 | 7 | DOW |
+------------------------------------+-----+-----+-----+-----+-----+-----+-----+-----+
| DATEPART(dw, /*Mon*/ ''20010101'') | 1 | 7 | 6 | 5 | 4 | 3 | 2 | 1 |
| DATEPART(dw, /*Tue*/ ''20010102'') | 2 | 1 | 7 | 6 | 5 | 4 | 3 | 2 |
| DATEPART(dw, /*Wed*/ ''20010103'') | 3 | 2 | 1 | 7 | 6 | 5 | 4 | 3 |
| DATEPART(dw, /*Thu*/ ''20010104'') | 4 | 3 | 2 | 1 | 7 | 6 | 5 | 4 |
| DATEPART(dw, /*Fri*/ ''20010105'') | 5 | 4 | 3 | 2 | 1 | 7 | 6 | 5 |
| DATEPART(dw, /*Sat*/ ''20010106'') | 6 | 5 | 4 | 3 | 2 | 1 | 7 | 6 |
| DATEPART(dw, /*Sun*/ ''20010107'') | 7 | 6 | 5 | 4 | 3 | 2 | 1 | 7 |
+------------------------------------+-----+-----+-----+-----+-----+-----+-----+-----+

La última columna contiene el valor ideal del día de la semana para las semanas de lunes a domingo *. Con solo mirar la tabla, obtenemos la siguiente ecuación:

(@@DATEFIRST + DATEPART(dw, SomeDate) - 1 - 1) % 7 + 1

2) Calcule el lunes y el domingo para una fecha determinada

Esto es trivial gracias al valor del día de la semana. Aquí hay un ejemplo:

WITH TestData(SomeDate) AS (
SELECT CAST(''20001225'' AS DATETIME) UNION ALL
SELECT CAST(''20001226'' AS DATETIME) UNION ALL
SELECT CAST(''20001227'' AS DATETIME) UNION ALL
SELECT CAST(''20001228'' AS DATETIME) UNION ALL
SELECT CAST(''20001229'' AS DATETIME) UNION ALL
SELECT CAST(''20001230'' AS DATETIME) UNION ALL
SELECT CAST(''20001231'' AS DATETIME) UNION ALL
SELECT CAST(''20010101'' AS DATETIME) UNION ALL
SELECT CAST(''20010102'' AS DATETIME) UNION ALL
SELECT CAST(''20010103'' AS DATETIME) UNION ALL
SELECT CAST(''20010104'' AS DATETIME) UNION ALL
SELECT CAST(''20010105'' AS DATETIME) UNION ALL
SELECT CAST(''20010106'' AS DATETIME) UNION ALL
SELECT CAST(''20010107'' AS DATETIME) UNION ALL
SELECT CAST(''20010108'' AS DATETIME) UNION ALL
SELECT CAST(''20010109'' AS DATETIME) UNION ALL
SELECT CAST(''20010110'' AS DATETIME) UNION ALL
SELECT CAST(''20010111'' AS DATETIME) UNION ALL
SELECT CAST(''20010112'' AS DATETIME) UNION ALL
SELECT CAST(''20010113'' AS DATETIME) UNION ALL
SELECT CAST(''20010114'' AS DATETIME)
), TestDataPlusDOW AS (
SELECT SomeDate, (@@DATEFIRST + DATEPART(dw, SomeDate) - 1 - 1) % 7 + 1 AS DOW
FROM TestData
)
SELECT
FORMAT(SomeDate, ''ddd yyyy-MM-dd'') AS SomeDate,
FORMAT(DATEADD(dd, -DOW + 1, SomeDate), ''ddd yyyy-MM-dd'') AS [Monday],
FORMAT(DATEADD(dd, -DOW + 1 + 6, SomeDate), ''ddd yyyy-MM-dd'') AS [Sunday]
FROM TestDataPlusDOW

Salida:

+------------------+------------------+------------------+
| SomeDate | Monday | Sunday |
+------------------+------------------+------------------+
| Mon 2000-12-25 | Mon 2000-12-25 | Sun 2000-12-31 |
| Tue 2000-12-26 | Mon 2000-12-25 | Sun 2000-12-31 |
| Wed 2000-12-27 | Mon 2000-12-25 | Sun 2000-12-31 |
| Thu 2000-12-28 | Mon 2000-12-25 | Sun 2000-12-31 |
| Fri 2000-12-29 | Mon 2000-12-25 | Sun 2000-12-31 |
| Sat 2000-12-30 | Mon 2000-12-25 | Sun 2000-12-31 |
| Sun 2000-12-31 | Mon 2000-12-25 | Sun 2000-12-31 |
| Mon 2001-01-01 | Mon 2001-01-01 | Sun 2001-01-07 |
| Tue 2001-01-02 | Mon 2001-01-01 | Sun 2001-01-07 |
| Wed 2001-01-03 | Mon 2001-01-01 | Sun 2001-01-07 |
| Thu 2001-01-04 | Mon 2001-01-01 | Sun 2001-01-07 |
| Fri 2001-01-05 | Mon 2001-01-01 | Sun 2001-01-07 |
| Sat 2001-01-06 | Mon 2001-01-01 | Sun 2001-01-07 |
| Sun 2001-01-07 | Mon 2001-01-01 | Sun 2001-01-07 |
| Mon 2001-01-08 | Mon 2001-01-08 | Sun 2001-01-14 |
| Tue 2001-01-09 | Mon 2001-01-08 | Sun 2001-01-14 |
| Wed 2001-01-10 | Mon 2001-01-08 | Sun 2001-01-14 |
| Thu 2001-01-11 | Mon 2001-01-08 | Sun 2001-01-14 |
| Fri 2001-01-12 | Mon 2001-01-08 | Sun 2001-01-14 |
| Sat 2001-01-13 | Mon 2001-01-08 | Sun 2001-01-14 |
| Sun 2001-01-14 | Mon 2001-01-08 | Sun 2001-01-14 |
+------------------+------------------+------------------+

_{* Para las semanas de domingo a sábado, debe ajustar la ecuación solo un poco, como agregar 1 en alguna parte.}

Answer 10

para Consultas de acceso, puede usar en el siguiente formato como campo

"FirstDayofWeek:IIf(IsDate([ForwardedForActionDate]),CDate(Format([ForwardedForActionDate],"dd/mm/yyyy"))-(Weekday([ForwardedForActionDate])-1))"

Cálculo directo permitido ..

Answer 11

también puedes usar esto:

 SELECT DATEADD(day, DATEDIFF(day, 0, WeddingDate) /7*7, 0) AS weekstart,
DATEADD(day, DATEDIFF(day, 6, WeddingDate-1) /7*7 + 7, 6) AS WeekEnd