Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Posts
    15

    Question Unanswered: Problems with week numbering

    Hi!

    I'm trying to get a MSSQL view to return some data marked with a week number based on a date (which is present on every row in the source table). Since I reside in Denmark, I would like the numbering to follow the rule '1. week of a year is the first week with a least 4 days in it'. My instance of MSSQL however is convinced that the rule '1. week starts 1. January'.

    Are there any ways to configure my way out of this problem??

    Examples:
    This year 1. and 2. January is returned as week=1, 3. January is returned as week 2.
    I would like 1. and 2. to be week=53 and 3. to be week=1.

    Thanx in advance!
    Ult

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    I'd define my own function returning the week number, based on SQL Server's definition (week 1 is the week of the 1st january) and my own offset per year, which is 0 or 1. You may consider to store the offset list as part of your function code, or as a table.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Sep 2003
    Posts
    15
    Thanx for input!

    I guess I'll be writing af function to do the dirty work, then. :-/

    I'm still looking for absolute knowledge on this issue: Is it really not something, u can configure?? :-)

  4. #4
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    You can configure the first day of a week

    SET DATEFIRST

    but I don't see how this would contribute to your problem. The only relevance is to make sure you have monday as your first day, to let SQL Server return the proper (according to its definition) week number.
    Last edited by DoktorBlue; 01-07-05 at 10:51.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  5. #5
    Join Date
    Sep 2003
    Posts
    15
    Yup! I've been grinding the SET DATEFIRST option, but haven't found any usefull applications of this feature yet (concerning my problems, that is! )

    As an aside i use @@datefirst to check the setting and act accordingly i the view (using CASE) - this is something I only do, because I haven't come across a way to set datefirst once and for all. I'm only able to manipulate it on a session basis??

    (Yes, my problems are numerous and by far outweighs my knowledge! )

    ..again: Thanx for input!

  6. #6
    Join Date
    Sep 2003
    Posts
    15
    Well, I have now written a function that calculates week according to ISO-8601. I found an algorithm here: http://personal.ecu.edu/mccartyr/ISOwdALG.txt (Thanx, Rick! )

    I can't shake the feeling that some where out there somebody is waiting to show me how to configure the server to use ISO-8601, so I now post my code to rattle the bush and get wiser!

    Thanx for any advice up until now - I'm still listening!

    The code is not optimized (havn't got that many data, so no use for now ), and please disregard the blatant lack of usefull coments!

    Code inserted:
    CREATE FUNCTION [dbo].[WeekISO8601] (
    @GregorianDate DATETIME
    )
    RETURNS INT AS
    BEGIN
    -- 1. Initialisations
    DECLARE @Y SMALLINT
    DECLARE @M SMALLINT
    DECLARE @D SMALLINT
    DECLARE @Yleap BIT
    DECLARE @YMOleap BIT
    DECLARE @Jan1wd TINYINT
    DECLARE @YearOut INT
    DECLARE @WeekOut INT

    SET @Y = YEAR(@GregorianDate)
    SET @M = MONTH(@GregorianDate)
    SET @D = DAY(@GregorianDate)

    -- 2. Find if year(GregorianDate) is leapyear
    IF (@Y % 4 = 0 AND @Y % 100 <> 0) OR @Y % 400 = 0
    SET @Yleap = 1
    ELSE
    SET @Yleap = 0

    -- 3. Find if year(GregorianDato)-1 is leapyear
    IF ((@Y-1) % 4 = 0 AND (@Y-1) % 100 <> 0) OR (@Y-1) % 400 = 0
    SET @YMOleap = 1
    ELSE
    SET @YMOleap = 0

    -- 5. Find the weekday of Jan 1. for year(GregorianDate)
    DECLARE @YY INTEGER
    DECLARE @C INTEGER
    DECLARE @G INTEGER

    SET @YY = (@Y-1) % 100
    SET @C = (@Y-1) - @YY
    SET @G = @YY + @YY / 4
    SET @Jan1wd = 1 + (((((@C / 100) % 4) * 5) + @G) % 7)

    -- 7. Find if GregorianDate falls in previous year, weeks 52 og 53
    IF @Jan1wd > 4 AND DATEPART(dy,@GregorianDate) <= (8 - @Jan1wd)
    BEGIN
    SET @YearOut = @Y - 1
    IF @Jan1wd = 5 OR (@Jan1wd = 6 AND @YMOleap = 1)
    SET @WeekOut = 53
    ELSE
    SET @WeekOut = 52
    END
    ELSE
    SET @YearOut = @Y

    -- 8. Find if GregorianDate falls in next year, week 1
    DECLARE @I INTEGER

    IF @YearOut = @Y
    BEGIN
    IF @Yleap = 1
    SET @I = 366
    ELSE
    SET @I = 365
    IF (@I - DATEPART(dy,@GregorianDate)) < (4 - DATEPART(dw,@GregorianDate))
    BEGIN
    SET @YearOut = @Y + 1
    SET @WeekOut = 1
    END
    END

    -- 9. Fin if GregorianDate falls in current year, weeks 1 through 53
    IF @YearOut = @Y
    BEGIN
    SET @WeekOut = DATEPART(dy,@GregorianDate) + ( 7 - DATEPART(dw,@GregorianDate)) + (@Jan1wd - 1)
    SET @WeekOut = @WeekOut / 7
    IF @Jan1wd > 4
    SET @WeekOut = @WeekOut - 1
    END

    RETURN @WeekOut


    END

  7. #7
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Good te hear that your problem is solved. I had a much more simple function in mind, using SQL Server's week number functionality, and substracting if applicable 1. This function is much more complex, but does not need any configurations.

    Greetings to the vikings
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  8. #8
    Join Date
    Sep 2003
    Posts
    15
    Well, I did consider the more simple solution, but since my work will surely be scrutinized by programmers from another section I decided to go for a more general solution. I guess we are all goldplaters in the end?

    I'm still afraid of being ridiculed by this other section, if my efford could have been replaced by a change in some part of the configuration, though?!

    Advice/absolute knowledge is still accepted!

    ..And a 'thank you' to DocB!

Posting Permissions

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