locked
How to get a week number as W01 for a given date using t-sql RRS feed

  • Question

  • Hi,

    How to get weeknumber as W01 for a given date which starts from 2010/01/01 ie friday.So week starting from friday 2010/01/01 to 2010/01/07 should have w01 for entire week and w02 for next week so on .

    Please advise as this is of high priority.

    Friday, October 25, 2013 11:14 AM

Answers

All replies

  • Hi Mad Oar,

    SELECT 'W'+cast(DATEPART( wk, '2010/01/01') as varchar(20))

    Hope this would give you the answer..

    Mark it as answered if it helped you.

    Thanks

    Shek


    • Edited by Eforc Friday, October 25, 2013 11:36 AM edit
    Friday, October 25, 2013 11:35 AM
  • Hi think you are considering today's date to compare with 01-01-2010 then below code should work.

    SELECT 'W'+CAST(DATEDIFF(wk,'2010-01-01',GETDATE()) AS VARCHAR) week_no


    sarat chandra sahoo

    Friday, October 25, 2013 11:48 AM
  • If you are this check for a wide range of dates build a Calender table and extract the information from it, that would a lot easier.

    http://letuslookintosqlserver.blogspot.in/2013/08/calender-table-in-sql-server.html


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, October 25, 2013 12:27 PM
  • Thanks for the response.Yeah thats give's weeknumber for that particular date..I have a startdate 01/01/2010 and end date 31/10/2013.I should get week number 1 as week starting from Friday to saturday as w01 so on for every year.

    ex:

    01/01/2010 Friday W01
    02/01/2010 Saturday W01
    03/01/2010 Sunday W01
    04/01/2010 Monday W01
    05/01/2010 Tuesday W01
    06/01/2010 Wednesday W01
    07/01/2010 Thursday W01
    Friday, October 25, 2013 12:52 PM
  • hi Mad Oar,

    Hope this should be your requirement.

    DECLARE @StartDate DATETIME
    SET @StartDate = '01/01/2013'
    
    DECLARE @EndDate DATETIME 
    SET @EndDate = GETDATE()
    
    DECLARE @TableOfDates TABLE(DateValue DATETIME)
    
    DECLARE @CurrentDate DATETIME
    
    SET @CurrentDate = @StartDate
    
    WHILE @CurrentDate <= @EndDate
    BEGIN
        INSERT INTO @TableOfDates(DateValue) VALUES (@CurrentDate)
    
        SET @CurrentDate = DATEADD(DAY, 1, @CurrentDate)
    END
    
    SELECT DateValue,'W'+cast(DATEPART(wk,DateValue) as varchar(30)) as 'week' FROM @TableOfDates

    Thanks

    Shek

    Please Mark it as answered if this was helpful to you..

    Friday, October 25, 2013 2:21 PM
  • Hi

    please try below code

    SELECT date1, 'W'+
    CASE when LEN(1+DATEDIFF(day,'2010-01-01',date1)/7) <= 1 then 
    '0'+CAST(1+DATEDIFF(day,'2010-01-01',date1)/7 AS VARCHAR)
    ELSE CAST(1+DATEDIFF(day,'2010-01-01',date1)/7 AS VARCHAR) END
     week_no
    from 
    (
    select convert(datetime,'01/01/2010') as date1 union all
    select convert(datetime,'01/02/2010') as date1 union all
    select convert(datetime,'01/03/2010') as date1 union all
    select convert(datetime,'01/04/2010') as date1 union all
    select convert(datetime,'01/05/2010') as date1 union all
    select convert(datetime,'01/06/2010') as date1 union all
    select convert(datetime,'01/07/2010') as date1 union all
    select convert(datetime,'01/08/2010') as date1 ) a

    Thanks

    Saravana Kumar C

    Friday, October 25, 2013 2:21 PM
  • Hi Shek,

    Thanks it meets my requirement to some extent when i change my start date as  01/02/2010 ie saturday my week starts..the result set as per your code gives me w2 not w1 which should be my ideal output..This is where iam stuck.Please suggest

    2010-01-02 00:00:00.000 W2
     2010-01-03 00:00:00.000 w2
    2010-01-04 00:00:00.000  w2
    2010-01-05 00:00:00.000  w2
    2010-01-06 00:00:00.000  w2
    2010-01-07 00:00:00.000   w2
    2010-01-08 00:00:00.000   w2    Thanks

    Friday, October 25, 2013 3:40 PM
  • Hi Mad Oar,

    So if my understanding is correct your server start date of the week  is saturday.Please Execute this to change to Sunday and your exact requirement will be obtained.

    Set DateFirst 7

    For more details about this you can refer to this link

    http://sqlserverrider.wordpress.com/2012/11/13/set-first-day-of-the-week/

    Thanks

    Shek

    Please Mark it as answered if this helped you.

    Friday, October 25, 2013 4:32 PM
  • Hi ,

    Like Shek suggested , Use DATEFIRST to set first day of a week and then generate week numbers and then by concatenating 'W' with  week number , you will get the desired output .

    If you want week numbering based on input start date you pass then you should try with Row_Number() .


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Friday, October 25, 2013 5:29 PM
  • Thanks for the response.Yeah thats give's weeknumber for that particular date..I have a startdate 01/01/2010 and end date 31/10/2013.I should get week number 1 as week starting from Friday to saturday as w01 so on for every year.

    I assume you have the dates and want to display the week number in specifc format

    DECLARE @D DATE='2013-10-05'																				
    SELECT DATENAME(WEEKDAY,@D),DATEPART(WEEK,@D),'W'+CASE WHEN DATEPART(WEEK,@D)<10 
    																				THEN '0'+CAST(DATEPART(WEEK,@D) AS VARCHAR)
    																			ELSE CAST(DATEPART(WEEK,@D) AS VARCHAR)
    																				END
    												AS WEEK_NUMBER


    Thanks, hsbal

    Friday, October 25, 2013 5:33 PM
  • Thanks for the response.Yeah thats give's weeknumber for that particular date..I have a startdate 01/01/2010 and end date 31/10/2013.I should get week number 1 as week starting from Friday to saturday as w01 so on for every year.

    ex:

    01/01/2010 Friday W01
    02/01/2010 Saturday W01
    03/01/2010 Sunday W01
    04/01/2010 Monday W01
    05/01/2010 Tuesday W01
    06/01/2010 Wednesday W01
    07/01/2010 Thursday W01
    If you want the week to be starting from Friday modify the calender table accordingly and query.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Saturday, October 26, 2013 6:00 AM
  • Not sure what are you looking for. Year does not start with FRIDAY every year.

    DECLARE @D DATE='2010-01-01'																				
    SELECT @D, DATENAME(WEEKDAY,@D),DATEPART(WEEK,@D), DATEPART(ISO_WEEK,@D)
    GO
    -- 2010-01-01	Friday	1	53
    
    DECLARE @D DATE='2011-01-01'																				
    SELECT @D, DATENAME(WEEKDAY,@D),DATEPART(WEEK,@D), DATEPART(ISO_WEEK,@D)
    GO
    -- 2011-01-01	Saturday	1	52
    
    DECLARE @D DATE='2012-01-01'																				
    SELECT @D, DATENAME(WEEKDAY,@D),DATEPART(WEEK,@D), DATEPART(ISO_WEEK,@D)
    GO
    -- 2012-01-01	Sunday	1	52
    
    DECLARE @D DATE='2013-01-01'																				
    SELECT @D, DATENAME(WEEKDAY,@D),DATEPART(WEEK,@D), DATEPART(ISO_WEEK,@D)
    GO
    -- 2013-01-01	Tuesday	1	1


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Saturday, October 26, 2013 6:24 AM
  • I did use set datefirst 6 and did generate the weeknumbers.But the result differs every year as it considers week 1 in previous year.Desired o/p required is-We should only have 52 weeks

    Fri,30/12/2010 w52

    sat,31/12/2011 W1

    Sat, 29/12/2012 W1

        

    Thanks

    Monday, October 28, 2013 1:12 PM
  • I assume from your format what you are really looking for is the ISO week.

    I would suggest you create a calendar table.

    Please see:

    http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/01/20/calendar-table-example-and-how-to-use-it.aspx

    Monday, October 28, 2013 1:43 PM
  • Yeah but iso weeks starts on Monday but my requirement is week starts on saturday irrespective of any year.
    Monday, October 28, 2013 4:48 PM
  • You will never be happy with DATEPART(WK. 

    If you use this: http://weblogs.sqlteam.com/dang/archive/2010/07/19/calendar-table-and-datetime-functions.aspx

    and set the DATEFIRST = sat, then I believe it will calculate the weeks correctly.

    Monday, October 28, 2013 4:58 PM