Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256

    Unanswered: database view with just current weekno

    I need a database view that will hold only one record with fields current year and current week. The weekno should follow the algoritm:
    week 1-53 with 1 january starting week 1.

    Anyone an idea?
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I am too lazy to look in the manual and verify this, but what about Julian day plus 6 days, then divide by 7, and then take the interger portion on that number.

  3. #3
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    That would account to the weeknumber from the start of the Julian counting (1-1-4713 BC) , so that is approximately 350.000 or so.
    I am looking for the weeknumber in the YEAR which starts with 1 and ends with 53............
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I thought there is a date format which returns the year, and the julian day within the year which is 1-366 (maybe DB2 calls it something else). If you select the day portion of that format, add 6, divide by 7, truncate to get just the integer portion of the division, that should do it (if you can get the date format I mentioned from DB2).

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    After looking in the manual, I was wrong.

    But how about taking the julian date (for the date in question) and subtracting the julian date of the first day of the year (for the year in question) to get the number of Julian Days for the year?

  6. #6
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    No, you were right all along (DAYOFYEAR function), but problem is to line it up properly to the weeknumber in the facttable:

    SELECT YEAR(CURRENT DATE),(DAYOFYEAR(CURRENT DATE))/7 FROM SYSIBM.SYSDUMMY1

    gives me 25 for weeknumber , which is 1 less than the 26 you get for this week, so I was thinking about:

    SELECT YEAR(CURRENT DATE),CEIL((DAYOFYEAR(CURRENT DATE))/7) FROM SYSIBM.SYSDUMMY1

    but that still gets me 25 instead of 26
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I think you have to add 6 days to the day of year, then divide the total by 7.

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Originally posted by blom0344

    SELECT YEAR(CURRENT DATE),CEIL((DAYOFYEAR(CURRENT DATE))/7) FROM SYSIBM.SYSDUMMY1

    but that still gets me 25 instead of 26
    I think the result gets cast into an integer _before_ CEIL(). Try this"

    Code:
    SELECT YEAR(CURRENT DATE), INTEGER(CEIL((DECIMAL(DAYOFYEAR(CURRENT DATE)))/7.0))  FROM SYSIBM.SYSDUMMY1

Posting Permissions

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