Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: Given a date, find the week number!

    Why are these seemingly simple problems always so darned complicated?!

    Problem:

    Given a date, find the week number



    Rules:

    Week 1 starts on the first Monday of the year
    Must work independant of SET DATEFIRST setting
    George
    Home | Blog

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Can you edit an ISO function to use Monday rather than Thursday?
    RMJCS > SQL Server > T/SQL Functions > ISO Week Number
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by myle View Post
    Clue Datediff() and ww

    DATEDIFF (Transact-SQL)
    Care to elaborate at this one?
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    In fact, I remember why that's not an option... from Poots' link
    Prior to SQL Server 2008, the DATEPART function supported a 'week' argument but it's not an ISO week number; the result is based on January 1st and is also dependant on the setting of the DATEFIRST session setting, which by default sets Sunday to be the first day of the week.
    George
    Home | Blog

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Poots, it looks to me that function works perfectly for my scenario without modification..?
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Any function that includes a variable called @intISOWeekdayNumberOfFirstOfThisThursdaysYear should be treated very seriously.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Yep, I'm happy enough with the solution you've linked to Poots! Cheers
    George
    Home | Blog

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quick lunch tinkering. Same logic, more efficient.
    Code:
    IF NOT EXISTS (SELECT NULL FROM sys.objects WHERE type_desc = 'SQL_INLINE_TABLE_VALUED_FUNCTION' AND object_id= OBJECT_ID('dbo.udf_GetISOWeekNumberFromDate_POOTS')) 
    BEGIN
        EXEC('
    CREATE FUNCTION  dbo.udf_GetISOWeekNumberFromDate_POOTS(@input  INT)
    RETURNS TABLE
    AS
    RETURN
        (
            SELECT  daCol   = NULL
         )')
    END
    GO
    
    ALTER FUNCTION dbo.udf_GetISOWeekNumberFromDate_POOTS
        (
              @date     DATETIME
        )
    RETURNS TABLE
    AS
    RETURN
        (
            SELECT  FirstThursdayOfYear         = DATEDIFF(d,DATEADD(d,(4 - ISOWeekdayNumberOfFirstOfThisThursdaysYear + CASE WHEN ISOWeekdayNumberOfFirstOfThisThursdaysYear IN (1,2,3,4) THEN 0 ELSE 7 END), FirstOfThisThursdaysYear), ThisThursday)/7+1
            FROM    
                    (   
                        SELECT  FirstOfThisThursdaysYear                    = CAST(CAST(YEAR(DATEADD(d,(4 - ((((DATEPART(dw, @date) - 1) + (@@DATEFIRST - 1)) % 7) + 1)), @date)) AS CHAR(4)) + '0101' AS DATETIME)
                              , ISOWeekdayNumberOfFirstOfThisThursdaysYear  = (((DATEPART(dw, CAST(CAST(YEAR(DATEADD(d,(4 - ((((DATEPART(dw, @date) - 1) + (@@DATEFIRST - 1)) % 7) + 1)), @date)) AS CHAR(4)) + '0101' AS DATETIME)) - 1) + (@@DATEFIRST - 1)) % 7) + 1
                              , ThisThursday                                = DATEADD(d,(4 - ((((DATEPART(dw, @date) - 1) + (@@DATEFIRST - 1)) % 7) + 1)), @date)
                    ) AS der_t
        )
    GO
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    ...aaaaaand they've changed their mind about how they calculate week numbers. FFS.

    Now the rule is: "Week 1 is the start of the week that contains the 1st of January. A week starts on a Monday."

    George
    Home | Blog

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I refer my dear friend to some sage Off Forum advice:
    Quote Originally Posted by The Wise and Learned Pootle
    go back to the client with specific questions to define exactly what the week number is
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Exactly what I did!

    Produced the reports to their specification and they came back saying it doesn't match their numbers because the week numbers split the data up wrong.

    b*******
    George
    Home | Blog

  13. #13
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    Or you could install cygwin and use the date utility.
    date -d "Jan 1 2010" +"%V"
    gives the week number for Jan 1 2010 with Monday as the first day of the week.

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Wish I could, but the database that I have to roll this for is locked down too tight; I've been refused a chance at rolling a CLR for this already so I doubt they'll let me use an external call to anything.
    George
    Home | Blog

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Hey Poots, where do we find a Wise and Learned Pootle?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •