Get the week start date and week end date from week number

Question

I have a query that counts member s wedding dates in the database  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  How do I work out when the start and end of each week represented in the result set  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

User · Answer

Let us break the problem down to two parts:

1) Determine the day of week

The DATEPART(dw, ...) returns a number, 1...7, relative to DATEFIRST setting (docs). The following table summarizes the possible values:

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

The last column contains the ideal day-of-week value for Monday to Sunday weeks*. By just looking at the chart we come up with the following equation:

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

2) Calculate the Monday and Sunday for given date

This is trivial thanks to the day-of-week value. Here is an example:

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

Output:

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

_{* For Sunday to Saturday weeks you need to adjust the equation just a little, like add 1 somewhere.}

User · Answer

Here is a DATEFIRST agnostic solution   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

User · Answer

for Access Queries  you can use in the below format as a field   FirstDayofWeek IIf IsDate  ForwardedForActionDate   CDate Format  ForwardedForActionDate   dd mm yyyy   - Weekday  ForwardedForActionDate  -1      direct Calculation allowed

User · Answer

Below query will give data between start and end of current week starting from sunday to saturday  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

User · Answer

Not sure how useful this is  but I ended up here from looking for a solution on Netezza SQL and couldn t find one on stack overflow    For IBM netezza you would use something  for week start mon  week end sun  like   select next day  WeddingDate   SUN   -6 as WeekStart   next day  WeddingDate   SUN   as WeekEnd

User · Answer

This is my solution        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

User · Answer

You can find the day of week and do a date add on days to get the start and end dates    DATEADD dd  - DATEPART dw  WeddingDate -1   WeddingDate   WeekStart   DATEADD dd  7- DATEPART dw  WeddingDate    WeddingDate   WeekEnd    You probably also want to look at stripping off the time from the date as well though

User · Answer

Expanding on  Tomalak s answer   The formula works for days other than Sunday and Monday but you need to use different values for where the 5 is   A way to arrive at the value you need is  Value Needed   7 -  Value From Date First Documentation for Desired Day Of Week  - 1   here is a link to the document  https   msdn microsoft com en-us library ms181598 aspx  And here is a table that lays it out for you               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   But you don t have to remember that table and just the formula  and actually you could use a slightly different one too the main need is to use a value that will make the remainder the correct number of days   Here is a working example   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   This method would be agnostic of the DATEFIRST Setting which is what I needed as I am building out a date dimension with multiple week methods included

User · Answer

Week Start  amp  End Date From Date For Power BI Dax Formula  WeekStartDate    DateColumn  -  WEEKDAY  DateColumn  -1  WeekEndDate    DateColumn     7-WEEKDAY  DateColumn

User · Answer

If sunday is considered as week start day  then here is the code  Declare  currentdate date    18 Jun 2020   select DATEADD D  - DATEPART WEEKDAY   currentdate  - 1    currentdate   select DATEADD D   7 - DATEPART WEEKDAY   currentdate     currentdate

User · Answer

Another way to do it   declare  week number int   6280 -- 2020-05-07 declare  start weekday int   0 -- Monday declare  end weekday int   6 -- next Sunday  select      dateadd week   week number   start weekday        dateadd week   week number   end weekday    Explanation     week number is the week number since the initial calendar date  1900-01-01   It can be computed this way  select datediff week  0   wedding date  as week number  start weekday for the week first day  0 for Monday  -1 if Sunday  end weekday for the week last day  6 for next Sunday  5 if Saturday dateadd week   week number   end weekday   adds the given number of weeks and the given number of days into the initial calendar date  1900-01-01

User · Answer

I have a way other  It is select day Start and day End of Week Current   DATEADD d  - DATEPART dw  GETDATE  -2    GETDATE    is date time Start and DATEADD day 7- DATEPART dw GETDATE  -1   GETDATE    is date time End

User · Answer

I just encounter a similar case with this one  but the solution here seems not helping me  So I try to figure it out by myself  I work out the week start date only  week end date should be of similar logic   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

User · Answer

Get Start Date  amp  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 DAY CASE WHEN DATEPART WEEKDAY   Date  1 THEN 0 ELSE  8 - DATEPART WEEKDAY   Date  END  CAST  Date AS DATE    Week End Date

User · Answer

The most voted answer works fine except for the 1st week and last week of a year  For example  if the value of WeddingDate is  2016-01-01   the result will be 2015-12-27 and 2016-01-02  but the right answer is 2016-01-01 and 2016-01-02   Try this   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    The result looks like    It works for all weeks  1st or others

User · Answer

Here is another version  If your Scenario requires Saturday to be 1st day of Week and Friday to be last day of Week  the below code will handle that     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

User · Answer

you can also use this     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

User · Answer

This doesn t come from me  but it got the job done regardless  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  I found it here

[sql] Get the week start date and week end date from week number

Examples related to sql

Examples related to sql-server

Examples related to date

Examples related to sql-server-2005

Examples related to week-number