locked
Date for week number RRS feed

  • Question

  • Hi All,

    I am using the following code to get the week number for a given date. 

    SELECT CAST(DATEPART(WK,Date) FROM tblDate

    Is is possible to get the date for that week number Monday being the first day of the week ?

    WeekNumber  Date

    1   01/03/2011

    2   01/10/2011

    ...........

    Thanks in advance!

     


    Sunday, June 19, 2011 1:27 PM

Answers

  • But how do you want the weeks numbered? Which is your week 1? The week with Jan 1st, which is how the WK arguments, and which is how they count weeks in the US.

    Or is week 1, the first week with at least four days in the new year, which is the ISO standard?

    A quick test indicates that the argument ISO_Week always counts week with Monday as the first day. Which aligns with ISO.

    For US week number, the setting of DATEFIRST controls what you get:

    set datefirst 1
    go
    select datepart(wk, '20110619'), datepart(wk, '20110620')
    go
    set datefirst 7
    go
    select datepart(wk, '20110619'), datepart(wk, '20110620')

    The result is

    25    26
    26    26 

    The argument ISO_Week is available only on SQL 2008. For earlier versions check the topic for CREATE FUNCTION in Books Online. There is an ISOWeek function among the examples.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, June 19, 2011 8:29 PM

All replies

  • How's this...
    SELECT DATEPART(WK, '2011-01-03') [Week No],
    	  CAST(DATEADD(D, (DATEDIFF(WK, '2011-01-03', GETDATE())) * -7, GETDATE() +1) AS DATE) [Date]
    	  
    

    Note that the above example is working with English dates - so you'll need to swap the Day / Month around.
    Zach Stagers - www.scratchbox.co.uk
    Sunday, June 19, 2011 1:56 PM
  • Try:

    DECLARE @Week char(7)
    SELECT
    @Week = '24/2011'

    SELECT
    DATEADD(wk, DATEDIFF(wk,0,RIGHT(@Week,4) + '0101' + DATEADD(d,
    LEFT(@Week,2) * 7, DATEPART(wk, RIGHT(@Week,4) + '0101') - (7 -
    @@DATEFIRST)) - @@DATEFIRST + 1), 0)

    Special Thanks to Frank

    Sunday, June 19, 2011 6:26 PM
  • But how do you want the weeks numbered? Which is your week 1? The week with Jan 1st, which is how the WK arguments, and which is how they count weeks in the US.

    Or is week 1, the first week with at least four days in the new year, which is the ISO standard?

    A quick test indicates that the argument ISO_Week always counts week with Monday as the first day. Which aligns with ISO.

    For US week number, the setting of DATEFIRST controls what you get:

    set datefirst 1
    go
    select datepart(wk, '20110619'), datepart(wk, '20110620')
    go
    set datefirst 7
    go
    select datepart(wk, '20110619'), datepart(wk, '20110620')

    The result is

    25    26
    26    26 

    The argument ISO_Week is available only on SQL 2008. For earlier versions check the topic for CREATE FUNCTION in Books Online. There is an ISOWeek function among the examples.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, June 19, 2011 8:29 PM