Подтвердить что ты не робот

Получить дату начала недели и дату окончания недели с номера недели в SQL Server

У меня есть запрос, который подсчитывает даты брака участника в базе данных...

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

Как мне разобраться, когда начало и конец каждой недели представлены в наборе результатов?

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
4b9b3361

ОТВЕТЫ

Ответ 1

Вы можете найти день недели и добавить дату в дни, чтобы получить даты начала и окончания.

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

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

Вероятно, вы также захотите посмотреть, как отключить время с даты.

Ответ 2

Вот агностическое решение DATEFIRST:

SET DATEFIRST 4     /* or use any other weird value to test it */
DECLARE @d DATETIME

SET @d = GETDATE()

SELECT
  @d ThatDate,
  DATEADD(dd, 0 - (@@DATEFIRST + 5 + DATEPART(dw, @d)) % 7, @d) Monday,
  DATEADD(dd, 6 - (@@DATEFIRST + 5 + DATEPART(dw, @d)) % 7, @d) Sunday

Ответ 3

вы также можете использовать это:

  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

Ответ 4

Вот еще одна версия. Если ваш сценарий требует, чтобы суббота была 1-й день недели и пятница, чтобы быть последним днем недели, нижеприведенный код будет обрабатывать:

  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

Screenshot of Query

Ответ 5

Ниже запрос даст данные между началом и концом текущей недели начиная с воскресенья до субботы

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())

Ответ 6

Развернувшись на @ответе Томалака. Формула работает в течение дней, отличных от воскресенья и понедельника, но вам нужно использовать разные значения для 5. Способ получения нужного вам значения

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

вот ссылка на документ: https://msdn.microsoft.com/en-us/library/ms181598.aspx

И вот таблица, которая излагает это для вас.

          | 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

Но вам не нужно помнить эту таблицу и просто формулу, и на самом деле вы могли бы использовать немного другую, поэтому главная потребность - использовать значение, которое сделает остаток правильным числом дней.

Вот рабочий пример:

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

Этот метод был бы агностиком DATEFIRST Setting, который мне нужен, поскольку я создаю измерение даты с включенными несколькими недельными методами.

Ответ 7

Разделим проблему до двух частей:

1) Определите день недели

DATEPART(dw, ...) возвращает число, 1... 7, относительно параметра DATEFIRST (docs). В следующей таблице приведены возможные значения:

                                                   @@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  |
+------------------------------------+-----+-----+-----+-----+-----+-----+-----+-----+

В последнем столбце содержится идеальное значение дня недели с понедельника по воскресенье *. Просто взглянув на диаграмму, получим следующее уравнение:

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

2) Рассчитайте понедельник и воскресенье для данной даты

Это тривиально благодаря значению дня недели. Вот пример:

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

Вывод:

+------------------+------------------+------------------+
|  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  |
+------------------+------------------+------------------+

* В период с воскресенья по субботу вам нужно немного настроить уравнение, например добавить 1 где-нибудь.

Ответ 8

Я просто сталкиваюсь с подобным случаем с этим, но решение здесь, похоже, не помогает мне. Поэтому я пытаюсь понять это сам. Я разрабатываю только дату начала недели, дата окончания недели должна иметь аналогичную логику.

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

Ответ 9

Самый проголосовавший ответ работает отлично, за исключением 1-й недели и последней недели года. Например, если значение WeddingDate равно "2016-01-01", результат будет 2015-12-27 и 2016-01-02, но правильный ответ 2016-01-01 и 2016-01-02.

Попробуйте следующее:

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;

Результат выглядит так: enter image description here

Он работает на протяжении всех недель, 1-го или других.

Ответ 10

Это не исходит от меня, но это сделало работу:

SELECT DATEADD(wk, -1, DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) --first day previous week
SELECT DATEADD(wk, 0, DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) --first day current week
SELECT DATEADD(wk, 1, DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) --first day next week

SELECT DATEADD(wk, 0, DATEADD(DAY, 0-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) --last day previous week
SELECT DATEADD(wk, 1, DATEADD(DAY, 0-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) --last day current week
SELECT DATEADD(wk, 2, DATEADD(DAY, 0-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) --last day next week

Я нашел его здесь.

Ответ 11

Начальная и конечная дата недели с даты для формулы мощности BI Dax

WeekStartDate = [DateColumn] - (WEEKDAY([DateColumn])-1)
WeekEndDate = [DateColumn] + (7-WEEKDAY([DateColumn]))

Ответ 12

Это мое решение

    SET DATEFIRST 1;    /* change to use a different datefirst  */
    DECLARE @date DATETIME
    SET @date = CAST('2/6/2019' as date)

    SELECT  DATEADD(dd,0 - (DATEPART(dw, @date) - 1) ,@date) [dateFrom], 
            DATEADD(dd,6 - (DATEPART(dw, @date) - 1) ,@date) [dateTo]

Ответ 13

Get Start date & End Date by Custom Date

 DECLARE @Date NVARCHAR(50)='05/19/2019' SELECT  DATEADD(DAY,CASE WHEN DATEPART(WEEKDAY, @Date)=1 THEN -6 ELSE 2 - DATEPART(WEEKDAY, @Date) END, CAST(@Date AS DATE)) [Week_Start_Date]

DATEADD (ДЕНЬ, СЛУЧАЙ, КОГДА DATEPART (WEEKDAY, @Date) = 1, ТО 0, 0 ELSE 8 - DATEPART (WEEKDAY, @Date) END, CAST (@Date AS DATE)) [Week_End_Date], DATEPART (WEEKDAY, @Date), DATEADD (ДЕНЬ, -6, CAST (@Date AS DATE))

Ответ 14

Не уверен, насколько это полезно, но я оказался здесь от поиска решения на Netezza SQL и не смог найти его при переполнении стека.

Для IBM netezza вы бы использовали что-то (для начала недели, выходное солнце), например:

выберите next_day (WeddingDate, 'SUN') -6 как WeekStart,

next_day (WeddingDate, 'SUN') как WeekEnd

Ответ 15

для запросов Access, вы можете использовать в нижнем формате как поле

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

прямой расчет разрешен.