How do I calculate the week number given a date?

  • hi guys

    How do I calculate the week number given a date for current year?

  • There are several concepts depending on what type week number you're looking for.

    If you need the week numbered as per the restrictions described in BOL (BooksOnLine, the SQL Server help system usually installed together with SQL server), section "dateparts [SQL Server], DATEPART" then DATEPART is one option.

    If you're looking for the ISO week number, then you might want to have a look at BOL, section "create function, Example A". But please be aware that the function is designed as a scalar-valued function that might have performance issues. You should modify it to an inline table-valued function.

    Another option would be to build a calendar table. Please search this site for related scripts. If you need further assistance please post back what you've tried so far and where you get stuck.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Hello,

    Easiest solution is using DATEPART function

    select DATEPART(WEEK,GETDATE())

    If that is not useful for your requirements, you can also think of to create date table in SQL Server

  • Going further from Eralper's idea;

    You can use

    select DATEPART(WEEK,DAY(getdate()))

    to get the week of the month.

    GETDATE() can be replaced by any datetime variable or even a string like '2010-12-07' ; i am using 'yyyy-dd-mm' format.

  • funooni (7/19/2010)


    Going further from Eralper's idea;

    To be fair, I think Lutz covered it first 🙂

    You can use select DATEPART(WEEK,DAY(getdate())) to get the week of the month. GETDATE() can be replaced by any datetime variable or even a string like '2010-12-07' ; i am using 'yyyy-dd-mm' format.

    This does not work. The DAY built-in function returns just the day date part of the supplied date, so for 19th July 2010, it would return 19.

    Supplying the value 19 to the DATEPART function results in 19 being implicitly converted to a date (19 days after the base date '1900-01-01') to give '1900-01-20'. Knowing the week number of 20th January 1900 is unlikely to be the desired result 😉

  • All marks go to Lutz 😉

    This does not work.

    Well i just checked it and it does work.

    The DAY built-in function returns just the day date part of the supplied date, so for 19th July 2010, it would return 19.

    Yes this would return 19

    Supplying the value 19 to the DATEPART function results in 19 being implicitly converted to a date (19 days after the base date '1900-01-01') to give '1900-01-20'. Knowing the week number of 20th January 1900 is unlikely to be the desired result

    Supplying the value value 19 to DATEPART function with the first option 'WEEK', it will return 3 which means this is the 3rd week of the month.

    One more thing to add the function DATEPART(WEEK,<ANY NUMBER FROM 0-364>) will return the current week number.

    For instance,

    select DATEPART(WEEK,21)

    will return 4 as 3 weeks have finished.

    Let me know please if this does not work and correct me if i am wrong at perceiving this.

    Thanks

  • funooni (7/19/2010)


    Well i just checked it and it does work. Supplying the value value 19 to DATEPART function with the first option 'WEEK', it will return 3 which means this is the 3rd week of the month.

    We're after the week number of the year here, not the week number of the month 🙂

    DECLARE @dt DATETIME;

    SET @dt = '2010-07-19';

    SELECT DATEPART(WEEK, DAY(@dt)); -- 3

    SELECT DATEPART(WEEK, @dt); -- 30

    The second example shows that 19th July 2010 is in week 30 of this year (with my current system settings).

    It's unclear to me what your code is showing. 19th July isn't in the third week of July on my calendar (July started on a Thursday, and weeks run from Monday for me).

    It is in the third week of July if you consider the first 7 days of any month to be the 'first week' and so on - is that the purpose of your function? If so, why does it return 4 for 20th July 2010? If I needed to know this sort of information, I would probably just use something like SELECT DAY(@dt) / 7 + 1; there's no need for the DATEPART at all.

    Paul

  • funooni (7/19/2010)


    ...

    Supplying the value value 19 to DATEPART function with the first option 'WEEK', it will return 3 which means this is the 3rd week of the month.

    One more thing to add the function DATEPART(WEEK,<ANY NUMBER FROM 0-364>) will return the current week number.

    For instance,

    select DATEPART(WEEK,21)

    will return 4 as 3 weeks have finished.

    Let me know please if this does not work and correct me if i am wrong at perceiving this.

    Thanks

    You are wrong at perceiving how DATEPART function works!

    And it works exactly as explained by Paul White NZ.

    Check the BoL.

    The second input parameter of DATEPART function is of DATETIME datatype.

    When you supply the integer there, it is implicitly converted into datetime as per following:

    1 Jan 1900 + INT value.

    You can supply positive or negative integer values as you wish...

    The number of week will properly match only for years which week days match ones of year 1900! Last such year was 2007 and the next one will be 2018!

    So if you run

    select DATEPART(WEEK,364)

    it will return 53 always, regardless when (which year) you run it as it represents 31 Dec 1900!

    But correct week number for 31 Dec 2000 would be 54 - leap year man!

    select DATEPART(WEEK,'31 Dec 2000')

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Like I stated in my first post: It depends on the requirement.

    You cannot use DATEPART() if you need to get the week as per ISO specification.

    Furthermore, DATEPART(WEEK,somedate) will return different results based on the setting of DATEFIRST.

    Straight from BOL:

    The week (wk, ww) datepart reflects changes made to SET DATEFIRST. January 1 of any year defines the starting number for the week datepart, for example: DATEPART(wk, 'Jan 1, xxxx') = 1, where xxxx is any year.

    Here are a few examples. Please note, that none of the DATEPART() function does return the correct ISO week and neither are the results consistent.

    DECLARE @date DATETIME

    SET @date='20100102'--Saturday, January 1st, as per ISO definition week 53 of year 2009

    SET DATEFIRST 1

    SELECT @@DATEFIRST AS DATEFIRST,DATEPART(WEEK,@date) AS WeekNo

    SET DATEFIRST 2

    SELECT @@DATEFIRST AS DATEFIRST,DATEPART(WEEK,@date) AS WeekNo

    SET DATEFIRST 3

    SELECT @@DATEFIRST AS DATEFIRST,DATEPART(WEEK,@date) AS WeekNo

    SET DATEFIRST 4

    SELECT @@DATEFIRST AS DATEFIRST,DATEPART(WEEK,@date) AS WeekNo

    SET DATEFIRST 5

    SELECT @@DATEFIRST AS DATEFIRST,DATEPART(WEEK,@date) AS WeekNo

    SET DATEFIRST 6

    SELECT @@DATEFIRST AS DATEFIRST,DATEPART(WEEK,@date) AS WeekNo

    SET DATEFIRST 7

    SELECT @@DATEFIRST AS DATEFIRST,DATEPART(WEEK,@date) AS WeekNo



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Thanks to all.

    Concepts cleared.

  • Eralper (7/19/2010)


    Hello,

    Easiest solution is using DATEPART function

    select DATEPART(WEEK,GETDATE())

    If that is not useful for your requirements, you can also think of to create date table in SQL Server

    It depends. What about ISO dates?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (7/19/2010)


    Eralper (7/19/2010)


    Hello,

    Easiest solution is using DATEPART function

    select DATEPART(WEEK,GETDATE())

    If that is not useful for your requirements, you can also think of to create date table in SQL Server

    It depends. What about ISO dates?

    Heh... looking back at some of the posts, I might be sorry for jumping in here. 😛

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (7/19/2010)


    ...

    Heh... looking back at some of the posts, I might be sorry for jumping in here. 😛

    Nothing to be sorry for!! You might be late but your input is always welcome! 😀



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Thank u guys...

    and one more thing my week is strat from saturday to friday

    and im enter today's date that giving me output will be like this:

    (dd/mm/yyyy)

    10/7/2010 - 16/7/2010

    3/7/2010 - 9/7/2010

    26/6/2010 - 2/7/2010

    19/6/2010 - 25/6/2010

    display last four week..

    thank u..

  • How abouth the following solution?

    DECLARE @date DATETIME

    SET @date=GETDATE()

    SELECT

    CONVERT(CHAR(10),DATEADD(wk, DATEDIFF(wk, 0, @date - 7*n), -2),103)

    + ' - '

    + CONVERT(CHAR(10),DATEADD(wk, DATEDIFF(wk, 0, @date - 7*(n-1)), -3),103)

    FROM

    (

    SELECT 1 AS n UNION ALL

    SELECT 2 UNION ALL

    SELECT 3 UNION ALL

    SELECT 4

    ) x

    Side note: I, personally, would still prefer using a calendar table over the posted solution...



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply