locked
How To Find Month Number using week number RRS feed

  • Question

  • Hi

    I have week number and Year, I want out put month and year

    Example :

    week no : 04

    Year : 2012

    Out put Date  should be : 012012

    Please suggest me

    Thursday, November 29, 2012 1:28 PM

Answers

  • declare @Year char(4)
    declare @WeekNumber tinyint
    Set @Year = '2012'
    Set @WeekNumber = 4
    select RIGHT('00' + CAST(month(dateadd(wk,@WeekNumber,@Year + '/01/01')) as varchar),2)  + @Year

    Something like this

    Chuck Pedretti | Magenic – North Region | magenic.com

    • Proposed as answer by RRavilla Thursday, November 29, 2012 1:45 PM
    • Marked as answer by Eileen Zhao Wednesday, December 5, 2012 7:40 AM
    Thursday, November 29, 2012 1:42 PM

All replies

  • HI Prateep,

    try this

    declare @Year char(4), @Week tinyint
    select @Year = '2012', @Week = 10
    select cast(month(dateadd(wk,@Week,@Year + '/01/01'))as nvarchar)+ @year

    Thanks

    RRavilla



    • Edited by RRavilla Thursday, November 29, 2012 1:44 PM
    Thursday, November 29, 2012 1:38 PM
  • if you have year 2012, then you could start with 01-01-2012 and add 4 weeks with DATEADD and then use MONTH with dat new date.
    Something like this in a derived colum or other ssis expression (not tested):

    First convert year to date:
    (DT_DBDATE)(([YEAR]*1000) + 101)      = 20120101

    Then add 4 weeks:
    DATEADD("WW", [WEEK], (DT_DBDATE)(([YEAR]*1000) + 101))

    Then get the month:
    MONTH(DATEADD("WW", [WEEK], (DT_DBDATE)(([YEAR]*1000) + 101)))

    Then add optional zero and add year
    RIGHT("0" + (dt_wstr, 2)MONTH(DATEADD("WW", [WEEK], (DT_DBDATE)(([YEAR]*1000) + 101))), 2) + (dt_wstr, 4)[YEAR]


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter


    • Edited by SSISJoostMVP Thursday, November 29, 2012 1:49 PM
    Thursday, November 29, 2012 1:38 PM
  • declare @Year char(4)
    declare @WeekNumber tinyint
    Set @Year = '2012'
    Set @WeekNumber = 4
    select RIGHT('00' + CAST(month(dateadd(wk,@WeekNumber,@Year + '/01/01')) as varchar),2)  + @Year

    Something like this

    Chuck Pedretti | Magenic – North Region | magenic.com

    • Proposed as answer by RRavilla Thursday, November 29, 2012 1:45 PM
    • Marked as answer by Eileen Zhao Wednesday, December 5, 2012 7:40 AM
    Thursday, November 29, 2012 1:42 PM
  • Thank You... Its working
    Thursday, November 29, 2012 2:10 PM