Get dates from a week number in T-SQL

Question

In Microsoft SQL Server  I have a week number    from DATEPART wk  datecol      But what I would like to do is turn this back into the date span for that week   For example    SELECT DATEPART wk  GETDATE      yields 10   I would like to derive 3 1 2009 and 3 7 2009 from this number   Is this possible

User · Answer

To answer your question   --CHANGE A WEEK NUMBER BACK INTO A DATE FOR THE FIRST DATE OF THE WEEK DECLARE  TaskWeek INT   17 DECLARE  TaskYear INT   2013  SELECT DATEADD WEEK   TaskWeek - 1 DATEADD dd  1 - DATEPART dw   1 1     CONVERT VARCHAR 4   TaskYear     1 1     CONVERT VARCHAR 4   TaskYear

User · Answer

Here you just have to pass year and week number   DECLARE  Year VARCHAR 4   SET  Year   2012   DECLARE  FirstDate DATETIME  SET  FirstDate    SELECT DATEADD dd 1  SELECT DATEADD wk DATEPART wk GETDATE   -1 Convert DAteTime  01-01-     Year                        DECLARE  LastDate DATETIME  SET  LastDate   SELECT DATEADD dd 4  FirstDate    SELECT  FirstDate          LastDate

User · Answer

You can set  WeekNum and  YearNum to whatever you want - in this example they are derived from the  datecol variable  which is set to GETDATE   for purposes of illustration   Once you have those values- you can calculate the date range for a week by using the following  DECLARE  datecol datetime   GETDATE    DECLARE  WeekNum INT          YearNum char 4    SELECT  WeekNum   DATEPART WK   datecol          YearNum   CAST DATEPART YY   datecol  AS CHAR 4     -- once you have the  WeekNum and  YearNum set  the following calculates the date range  SELECT DATEADD wk  DATEDIFF wk  6   1 1      YearNum      WeekNum-1   6  AS StartOfWeek  SELECT DATEADD wk  DATEDIFF wk  5   1 1      YearNum      WeekNum-1   5  AS EndOfWeek

User · Answer

If your week starts from Monday  on SQL Server 2008   select datecol    DATEPART ISOWK  datecol  as week      DATEPART dw  datecol  5  7  1 as weekday     DATEADD dd  -  DATEPART dw  datecol  5  7   datecol   as Monday     DATEADD dd  -  DATEPART dw  datecol  5  7  6  datecol   as Sunday

User · Answer

I didn t take the time to test out every answer on here  but nothing seems as simple and as efficient as this   DECLARE  WeekNum int DECLARE  YearNum char 4   SELECT DATEADD wk  DATEDIFF wk  6   1 1      YearNum      WeekNum-1   6  AS StartOfWeek  SELECT DATEADD wk  DATEDIFF wk  5   1 1      YearNum      WeekNum-1   5  AS EndOfWeek

User · Answer

DECLARE  dayval int    monthval int    yearval int  SET  dayval   1 SET  monthval   1 SET  yearval   2011   DECLARE  dtDateSerial datetime          SET  dtDateSerial   DATEADD day   dayval-1                                  DATEADD month   monthval-1                                      DATEADD year   yearval-1900  0                                                                   DECLARE  weekno int SET  weekno   53   DECLARE  weekstart datetime SET  weekstart   dateadd day  7     weekno -1  - datepart  dw   dtDateSerial    dtDateSerial   DECLARE  weekend datetime SET  weekend   dateadd day  6   weekstart   SELECT  weekstart   weekend

User · Answer

Another way to do it   declare  week number int  declare  start weekday int   0 -- Monday declare  end weekday int   6 -- next Sunday  select  week number   datediff week  0  getdate     select      dateadd week   week number   start weekday  as WEEK FIRST DAY       dateadd week   week number   end weekday  as WEEK LAST DAY   Explanation     week number is computed based on the initial calendar date  1900-01-01   Replace getdate   by whatever date you want   start weekday is 0 if Monday  If Sunday  then declare it as -1  end weekday is 6 if next Sunday  If Saturday  then declare it as 5 Then dateadd function  will add the given number of weeks and the given number of days to the initial calendar date  1900-01-01

User · Answer

How about a function that jumps to the week before that week number and then steps through the next few days until the week number changes  max 7 steps   returning the new date   CREATE FUNCTION dbo fnGetDateFromWeekNo   weekNo int    yearNo  int  RETURNS smalldatetime AS BEGIN   DECLARE  tmpDate smalldatetime   set  tmpdate  cast cast   yearNo as varchar     -01-01  as smalldatetime  -- jump forward x-1 weeks to save counting through the whole year  set  tmpdate dateadd wk  weekno-1  tmpdate   -- make sure weekno is not out of range if  WeekNo  lt   datepart wk cast cast   yearNo as varchar     -12-31  as smalldatetime   BEGIN     WHILE  datepart wk  tmpdate  lt  WeekNo      BEGIN         set  tmpdate dateadd dd 1  tmpdate      END END ELSE BEGIN     -- invalid weeknumber given     set  tmpdate null END   RETURN  tmpDate  END

User · Answer

This works for me   select      convert varchar 50   dateadd dd  - datepart dw  DATECOL    1  DATECOL   101       convert varchar 50   dateadd dd  - datepart dw  DATECOL    7  DATECOL   101

User · Answer

SELECT DATEADD week   weekNumber - 1  DATEADD DAY    datefirst - DATEPART weekday  CAST YEAR GETDATE    AS VARCHAR     -01-01   - 6  CAST YEAR GETDATE    AS VARCHAR     -01-01

User · Answer

declare  IntWeek as varchar 20  SET  IntWeek    201820   SELECT      DATEADD wk  DATEDIFF wk    DATEFIRST  LEFT  IntWeek 4     -01-01          cast RIGHT  IntWeek  2  as int  -1     DATEFIRST  AS StartOfWeek

User · Answer

dateadd    dd     datepart wk   Date  7     convert smalldatetime  convert char year max  Date    convert char   -01-01     -1

User · Answer

SELECT DATECOL - DATEPART weekday  DATECOL   DATECOL - DATEPART weekday  DATECOL    7

User · Answer

This should work regardless of   DATEFIRST  ALTER FUNCTION dbo DEV VW WeekSerial       YearNum int       WeekNum int       DayNum int      RETURNS Date AS      BEGIN          DECLARE  FirstDayYear As Date           SET  FirstDayYear  01 01     CAST  YearNum As varchar           RETURN dateadd d   DayNum-datepart weekday  FirstDayYear   dateadd week   WeekNum-1  FirstDayYear        END

User · Answer

The most votes answer works fine except the 1st week and last week of year  When datecol value is  2009-01-01   the result will be 01 03 2009 and 12 28 2008   My solution   DECLARE  Date date    2009-03-01    WeekNum int   StartDate date  SELECT  WeekNum   DATEPART WEEK   Date   SELECT  StartDate   DATEADD DAY  - DATEPART WEEKDAY  DATEADD YEAR  DATEDIFF YEAR  0   Date   0     6   DATEADD YEAR  DATEDIFF YEAR  0   Date   0    SELECT CONVERT nvarchar  CASE WHEN  WeekNum   1 THEN CAST DATEADD YEAR  DATEDIFF YEAR  0   Date   0  AS date  ELSE DATEADD DAY  7    WeekNum   StartDate  END  101  AS StartOfWeek        CONVERT nvarchar  CASE WHEN  WeekNum   DATEPART WEEK  DATEADD DAY  -1  DATEADD YEAR  DATEDIFF YEAR  0   Date    1  0    THEN DATEADD DAY  -1  DATEADD YEAR  DATEDIFF YEAR  0   Date    1  0   ELSE DATEADD DAY  7    WeekNum   6   StartDate  END  101  AS EndOfWeek    This will display 01 01 2009 and 01 03 2009 for the 1st week  and display 03 01 2009 and 03 07 2009 for the 10th week   I think this would be what you want exactly  You can replace the variables with their expressions as you wish

User · Answer

Give it  Year and  Week   return first date of that week   Declare  Year   int   Week int   YearText varchar 4   set  Year   2009 set  Week   10  set  YearText    Year  print dateadd day               1 - datepart dw   YearText    -01-01                       Week-1    7                YearText    -01-01

User · Answer

Quassnoi s answer works  but kind of leaves you on the hook for cleaning up the dates if they are dates in the middle of the day  his start of week leaves you one day earlier than you need to be if you use a time in the middle of the day -- you can test using GETDATE      I ve used something like this in the past   SELECT     CONVERT varchar 50    DATEADD dd    DATEFIRST - DATEPART dw  DATECOL   DATECOL    101      CONVERT varchar 50    DATEADD dd    DATEFIRST - DATEPART dw  DATECOL  - 6  DATECOL    101    A side benefit of this is that by using   DATEFIRST you can handle nonstandard week starting days  the default is Sunday  but with SET   DATEFIRST you can change this    It seems crazy that simple date manipulation in SQL Server has to be this arcane  but there you go

User · Answer

I ve taken elindeblom s solution and modified it - the use of strings  even if cast to dates  makes me nervous for the different formats of dates used around the world  This avoids that issue    While not requested  I ve also included time so the week ends 1 second before midnight       DECLARE  WeekNum INT   12           YearNum INT   2014        SELECT  DATEADD wk                      DATEDIFF wk  6                               CAST RTRIM  YearNum   10000   1   100   1  AS DATETIME                            WeekNum - 1    6  AS  start of week               DATEADD second  -1                      DATEADD day                              DATEDIFF day  0                                       DATEADD wk                                               DATEDIFF wk  5                                                        CAST RTRIM  YearNum   10000                                                                    1   100   1  AS DATETIME                                                     WeekNum   -1    5     1  0   AS  end of week      Yes  I know I m still casting but from a number  It  feels  safer to me   This results in       start of week           end of week     ----------------------- -----------------------     2014-03-16 00 00 00 000 2014-03-22 23 59 59 000

User · Answer

I just incorporated the SELECT with a CASE statement  For my situation Monday marked the first day of the week  and didn t want to deal with the SET DATEFIRST command   CASE DATEPART dw  lt YourDateTimeField gt      WHEN 1 THEN CONVERT char 10   DATEADD DD  -6   lt YourDateTimeField gt   126       to     CONVERT char 10    lt YourDateTimeField gt  126     WHEN 2 THEN CONVERT char 10    lt YourDateTimeField gt  126       to     CONVERT char 10   DATEADD DD  6   lt YourDateTimeField gt   126     WHEN 3 THEN CONVERT char 10   DATEADD DD  -1   lt YourDateTimeField gt   126       to     CONVERT char 10   DATEADD DD  5   lt YourDateTimeField gt   126     WHEN 4 THEN CONVERT char 10   DATEADD DD  -2   lt YourDateTimeField gt   126       to     CONVERT char 10   DATEADD DD  4   lt YourDateTimeField gt   126     WHEN 5 THEN CONVERT char 10   DATEADD DD  -3   lt YourDateTimeField gt   126       to     CONVERT char 10   DATEADD DD  3   lt YourDateTimeField gt   126     WHEN 6 THEN CONVERT char 10   DATEADD DD  -4   lt YourDateTimeField gt   126       to     CONVERT char 10   DATEADD DD  2   lt YourDateTimeField gt   126     WHEN 7 THEN CONVERT char 10   DATEADD DD  -5   lt YourDateTimeField gt   126       to     CONVERT char 10   DATEADD DD  1   lt YourDateTimeField gt   126     ELSE  UNK  END AS Week Range

User · Answer

Answer   select  DateAdd day -DATEPart DW  lt Date gt     lt Date gt    FirstDayOfWeek   DateAdd day -DATEPart DW  lt Date gt   6   lt Date gt    LastDayOfWeek  FROM  lt TABLE gt

[sql-server] Get dates from a week number in T-SQL

Examples related to sql-server

Examples related to tsql

Examples related to date