Post reply

Week Number of a specific Date

  • May 24, 2010 at 1:16 am

    #139157

    Can anybody tell me to How to get the week number of a specific Date in SQL Server.

    Like : say Date "05/03/2010" belongs to the 1st week of May year 2010,

    So, the same way I need to know the week number of a given (variable) date.

    I need to write / use a Function in SQL server which will return the Number (INT) (i.e. 1, 2 etc...) against a specific (Parameter) date.

  • May 24, 2010 at 2:33 am

    #1171413

    Seems like a job for a calendar table

    http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html


    Clear Sky SQL
    My Blog[/url]

  • May 24, 2010 at 2:44 am

    #1171419

    See this

    DECLARE @dt DATETIME, @WeekNumber INT

    SET @dt='03/01/2010'

    SELECT @WeekNumber=DATEPART(wk,@DT)

    SELECT @WeekNumber

  • May 24, 2010 at 2:56 am

    #1171424

    Gopi Muluka (5/24/2010)

    See this

    DECLARE @dt DATETIME, @WeekNumber INT

    SET @dt='03/01/2010'

    SELECT @WeekNumber=DATEPART(wk,@DT)

    SELECT @WeekNumber

    You need to be careful with DATEPART(wk,...) since it depends on the setting of DATEFIRST. Furthermore, it always starts with week#1 for January 1st, which is not always true for ISO weeks.

    Like Dave recommended: use a calendar table or you'd have to write your own getWeekFromDate function. Side note: the one shown in BOL (BooksOnLine) will give the correct result but you should modify it to be used as a iTVF (inline table-valued function). Otherwise this function may cause performance issues.

    Edit: typo fixed


    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]

  • May 28, 2010 at 7:27 am

    #1173909

    Vijay,

    I would take a look at this workbench off of SimpleTalk. It explains everything you are looking for and then some in depth, and also shows multiple ways of achieving the same results.

    Using Dates and Times in SQL Server (By: Robyn Page)

    http://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-datetime-workbench/

    Edit - This is a DATE-TIME function workbench. Demonstrates many uses of most of the DATE-TIME commands.

    Danny Sheridan
    Comtekh, Inc.

Viewing 5 posts - 1 through 4 (of 4 total)

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