none
return the end of week date using month and week number

    Question

  • Hi all,

    I am trying to find the date for end of the week using given month (201401, 201402...i.e, 2014 is year and 01,02 are months) and week number (01,02,03...) for the year. For example if my month is 201402 and week_number is 06 then it return the week_date as 2014-02-08. Can you please help me in writing a sql statement for this scenario.

    Thanks in advance,

    Nikhil

    Thursday, March 27, 2014 8:00 PM

Answers

  • You can easily achieve with a calendar table, if you have one.

    or else use below..

    DECLARE @WeekNo int= 14, @YearMonth int=201401

    SELECT DATEADD(wk,@WeekNo-1,DATEADD(yy,LEFT(@YearMonth, 4)-1900,0)) AS WeekStart,
    DATEADD(wk,@WeekNo,DATEADD(yy, LEFT(@YearMonth, 4)-1900,0)) AS WeekEnd

    Please mark as answer, if this has helped you solve the issue.

    Good Luck :) .. visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.



    Thursday, March 27, 2014 8:25 PM

All replies

  • You can easily achieve with a calendar table, if you have one.

    or else use below..

    DECLARE @WeekNo int= 14, @YearMonth int=201401

    SELECT DATEADD(wk,@WeekNo-1,DATEADD(yy,LEFT(@YearMonth, 4)-1900,0)) AS WeekStart,
    DATEADD(wk,@WeekNo,DATEADD(yy, LEFT(@YearMonth, 4)-1900,0)) AS WeekEnd

    Please mark as answer, if this has helped you solve the issue.

    Good Luck :) .. visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.



    Thursday, March 27, 2014 8:25 PM
  • create table test (yyyymm int, weekNum int)
    Insert into test values(201402,6), (201401,1)
    
       select 
       --Dateadd(day, ((datepart(weekday,'1/1/2014')-7)+ weeknum*7)-1,
       Dateadd(day, ((datepart(weekday, dateadd(year, Datediff(year, 0, Cast(cast(yyyymm as varchar(6))+'01' as datetime)),0 ) )-7)+ weeknum*7)-1,
       Dateadd(year, datediff(year, 0, Cast(cast(yyyymm as varchar(6))+'01' as datetime)),0 )) 
       from test
    
    
       drop table test

    Thursday, March 27, 2014 9:22 PM
  • Current month is irrelevant

    with dt as (
       select dateadd(day, (6-1)*7, '2014-01-01') as xwk
    )
    select dateadd(day, 7-datepart(weekday, dt.xwk), xwk)
    from dt;

    Change the "6" in the with statement to the week of interest and 2014 to the year of interest...

    Thursday, March 27, 2014 10:29 PM
  • This takes it one step further, and will get you the date at end of the week that you were looking for. It doesn't assume that week 1 starts on Wednesday January 1st:

    SET DATEFIRST 6
    declare @wk int  set @wk = 6
    declare @yr int  set @yr = 2014
    
    select dateadd (week, @wk, dateadd (year, @yr-1900, 0)) - 4 - datepart(dw, dateadd (week, @wk, dateadd (year, @yr-1900, 0)) - 4) + 1


    Martina White

    Friday, March 28, 2014 4:08 PM