Login or Sign Up to become a member!
LessThanDot Sit Logo

LessThanDot

Data Management

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot friendfeed Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Your profile

    Tools

    Tag cloud

    azure book business intelligence ctp database dates denali dmv functions gotcha high availability how to howto indexing mirroring performance sql sql friday sql server sql server 2000 sql server 2005 sql server 2008 sql server 2008 r2 sql server 2012 sql server denali sql server integration services ssis t-sql tip trick

    Authors

    Main Categories

    Search

    XML Feeds

    What is RSS?

    Google Ads

    « SQL Server 2008 Cumulative Update 1 is available from MicrosoftInterview With Louis Davidson Author of Pro SQL Server 2008 Relational Database Design and Implementation »
    11
    comments

    ISO Week In SQL Server

    by SQLDenis on Sep 22, 2008 in categories Data Modelling & Design
    Instapaper

    ISO Week in SQL Server

    First let's take a look at what ISO week is, from WikiPedia:
    Week date representations are in the format as shown below.
    YYYY-Www or YYYYWww
    YYYY-Www-D or YYYYWwwD

    [YYYY] indicates the so-called ISO year which is slightly different than the calendar year (see below). [Www] is the week number prefixed by the letter 'W', from W01 through W53. [D] is the weekday number, from 1 through 7, beginning with Monday and ending with Sunday. This form is popular in the manufacturing industries.
    There are mutually equivalent definitions for week 01:

    • the week with the year's first Thursday in it,
    • the week with 4 January in it,
    • the first week with the majority (four or more) of its days in the starting year, and
    • the week starting with the Monday in the period 29 December – 4 January.

    If 1 January is on a Monday, Tuesday, Wednesday or Thursday, it is in week 01. If 1 January is on a Friday, Saturday or Sunday, it is in week 52 or 53 of the previous year.
    The week number can be described by counting the Thursdays: week 12 contains the 12th Thursday of the year.
    The ISO year starts at the first day (Monday) of week 01 and ends at the Sunday before the new ISO year (hence without overlap or gap). It consists of 52 or 53 full weeks. The ISO year number deviates from the number of the calendar year (Gregorian year) on a Friday, Saturday, and Sunday, or a Saturday and Sunday, or just a Sunday, at the start of the calendar year (which are at the end of the previous ISO year) and a Monday, Tuesday and Wednesday, or a Monday and Tuesday, or just a Monday, at the end of the calendar year (which are in week 01 of the next ISO year). For Thursdays, the ISO year number is always equal to the calendar year number.
    Examples:

    • 2008-12-29 is written "2009-W01-1"
    • 2010-01-03 is written "2009-W53-7"

    You can read more about ISO week here: http://en.wikipedia.org/wiki/ISO_week_date

    Sometimes you need to show the ISO week in SQL server but there was no built in way to calculate it until SQL server 2008 was released. In SQL Server 2000/2005 you could use the user defined function ISOweek which was in the SQL Server books on line.
    Here is what the function looks like

    tsql
    Line number Off | Hide | Select all
    1. CREATE FUNCTION ISOweek  (@DATE datetime)
    2. RETURNS int
    3. AS
    4. BEGIN
    5.    DECLARE @ISOweek int
    6.    SET @ISOweek= DATEPART(wk,@DATE)+1
    7.       -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104')
    8. --Special cases: Jan 1-3 may belong to the previous year
    9.    IF (@ISOweek=0)
    10.       SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1
    11.          AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
    12. --Special case: Dec 29-31 may belong to the next year
    13.    IF ((DATEPART(mm,@DATE)=12) AND
    14.       ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
    15.       SET @ISOweek=1
    16.    RETURN(@ISOweek)
    17. END
    18. GO

    Now run the following query on SQL server 2000 and up

    tsql
    Line number Off | Hide | Select all
    1. select dbo.ISOweek('20071231'),datepart(wk,'20071231')


    If you are running SQL server 2008 then you can use DATEPART and the datepart argument isowk. Run the select statement below to see the result

    tsql
    Line number Off | Hide | Select all
    1. select datepart(isowk,'20071231'),datepart(wk,'20071231')

    As you can see here also SQL Server's wk part returns 53 while isowk returns 1

    I have also added parts of this to the wiki here: ISO Week In SQL Server

    About the Author

    User bio imageDenis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
    Social SitingsTwitterFacebookLinkedInHomePageFlickrLTD RSS Feed
    22999 views
    Read More Posts by SQLDenis
    Instapaper

    11 comments

    Comment from: Everybody [Visitor]
    Everybody I've found everywhere this code. But it isn't working ! Try with 2008.08.31. The isoweek must be 35 instead of 36 !
    10/09/08 @ 06:51
    Comment from: SQLDenis [Member] Email
    SQLDenis Yep, you are right

    However SELECT DATEPART(isowk,'20080831') does return the correct date

    Funny (or sad) how that is wrong in Books On Line
    10/09/08 @ 07:07
    Comment from: Everybody [Visitor]
    Everybody Yes, but I have SQL 2005 Server. I can not use the isowk parameter.... That is why I am searching a right function...
    10/09/08 @ 07:16
    Comment from: Kenta [Visitor]
    Kenta The function relies on the first day of week being monday.

    Try this:
    SET DATEFIRST 1 -- Set the first day of the week to Monday
    SELECT dbo.ISOweek('20080831')
    10/13/08 @ 01:08
    Comment from: Rhys [Visitor] · http://www.rmjcs.com
    Rhys Prior to SQL Server 2008, getting the ISO week number, regardless of the DATEFIRST session setting, required a little more effort. See my ISO Week Number page at http://www.rmjcs.com/SQLServer/TSQLFunctions/ISOWeekNumber/tabid/207/Default.aspx for a widely used T/SQL function to do the job.

    Regards,
    Rhys
    01/05/10 @ 15:03
    Comment from: Rogue [Visitor]
    Rogue Rhys definitely score! thanks!
    01/08/10 @ 01:06
    Comment from: Mikey [Visitor]
    Mikey Very nice Rhys - thanks!
    01/12/10 @ 07:36
    Comment from: Eric [Visitor]
    Eric But what about the YYYY portion of the week?
    the first 3 days of 2010 are considered week 53 for 2009.
    In Oracle I can use the 'IYYY' (vs. YYYY) argument to return
    the ISO Year to go with the ISO Week.
    01/27/10 @ 16:41
    Comment from: TriggerHappy [Visitor]
    TriggerHappy CASE WHEN dbo.ISOWeek('2010-01-01') > 52 THEN DATEPART(year, '2010-01-01') - 1 ELSE DATEPART(year, '2010-01-01') END [ISOYear]
    02/03/10 @ 23:36
    Comment from: Eric [Visitor]
    Eric For purposes of showing a solution, yes; but way over simplified.
    If MS is going to go as far as creating an ISO Week function (SQL 2008) I find it hard to believe that they wouldn't include one for the ISO Year.
    It doesn't make much sense to have one without the other.
    02/09/10 @ 15:30
    Comment from: Flugs [Visitor]
    Flugs Mir hat ISOWeek nicht viel geholfen, da Sie falsche KW liefert (Bsp. 31.12.2009 und 1.1.2010 beide KW 53 ISOWeek liefert 52 und 53).
    Nach langem Suchen habe ich eine Funktion gefunden, welche die richtige KW zurückgibt. Ich habe sie um das Jahr ergänzt (Ausgabe z.B. '53/2009'):

    Create FUNCTION [dbo].[ISOWeek](@dtDate as DATETIME) RETURNS nvarchar(7) WITH RETURNS NULL ON NULL INPUT
    /*
    ** Return the ISO week of the year regardless of the DATEFIRST session setting.
    */
    AS
    BEGIN

    DECLARE @intISOWeekdayNumber INT
    DECLARE @dtThisThursday DATETIME
    DECLARE @dtFirstOfThisThursdaysYear DATETIME
    DECLARE @intISOWeekdayNumberOfFirstOfThisThursdaysYear INT
    DECLARE @dtFirstThursdayOfYear DATETIME
    DECLARE @intISOWeekNumber INT
    Declare @intYear Int

    -- Get the ISO week day number (Monday = 1) for our date.
    SET @intISOWeekdayNumber = (((DATEPART(dw, @dtDate) - 1) + (@@DATEFIRST - 1)) % 7) + 1

    -- Get the date of the Thursday in this ISO week.
    SET @dtThisThursday = DATEADD(d,(4 - @intISOWeekdayNumber),@dtDate)

    -- Get the date of the 1st January for 'this Thursdays' year.
    SET @dtFirstOfThisThursdaysYear = CAST(CAST(YEAR(@dtThisThursday) AS CHAR(4)) + '-01-01' AS DATETIME)

    SET @intISOWeekdayNumberOfFirstOfThisThursdaysYear = (((DATEPART(dw, @dtFirstOfThisThursdaysYear) - 1) + (@@DATEFIRST - 1)) % 7) + 1

    -- Get the date of the first Thursday in 'this Thursdays' year.
    -- The year of which the ISO week is a part is the year of this date.
    IF (@intISOWeekdayNumberOfFirstOfThisThursdaysYear in (1,2,3,4))
    SET @dtFirstThursdayOfYear = DATEADD(d,(4 - @intISOWeekdayNumberOfFirstOfThisThursdaysYear),@dtFirstOfThisThursdaysYear)
    ELSE
    SET @dtFirstThursdayOfYear = DATEADD(d,(4 - @intISOWeekdayNumberOfFirstOfThisThursdaysYear + 7),@dtFirstOfThisThursdaysYear)

    -- Work out how many weeks from the first Thursday to this Thursday.
    SET @intISOWeekNumber = DATEDIFF(d,@dtFirstThursdayOfYear,@dtThisThursday)/7+1
    if (Datepart(mm,@dtDate)=1 and @intISOWeekNumber > 50)
    Set @intYear = Datepart(yyyy,@dtDate)-1
    else if (Datepart(mm,@dtDate)=12 and @intISOWeekNumber = 1)
    Set @intYear = Datepart(yyyy,@dtDate)+1
    else
    set @intYear = Datepart(yyyy,@dtDate)

    RETURN (cast(@intISOWeekNumber as nvarchar) + '/' + cast(@intYear as nvarchar))

    END
    12/10/10 @ 03:22

    Leave a comment


    Your email address will not be revealed on this site.

    Your URL will be displayed.
    (Line breaks become <br />)
    (Name, email & website)
    (Allow users to contact you through a message form (your email will not be revealed.)

    Powered by B2Evolution
    Valid XHTML | Valid CSS