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

    Unanswered: Problems with DAYS() function

    I have a funny situation with using the DAYS() function:

    1.Select DAYS(CURRENT DATE) - DAYS(FIRSTDAY) from TABLE

    gives incorrect output (far too high)

    2.Select RESUMEDDAY - FIRSTDAY from TABLE

    (both datefields) gives proper results.

    3.Select CURRENT DATE - FIRSTDAY from TABLE

    returns ridiculous high integers, even much higher than 1.

    4. Select TIMESTAMPDIFF(16,CHAR(CURRENT DATE - FIRSTDAY)) from TABLE

    returns about the same output as 1. , but slightly lower

    What am i missing to work with the system date ?
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I think your problem is how date, time, and timestamp arithmatic is done.

    Using your examples:

    1) will return the number of days between the two dates as an integer

    2) and 3) return a duration (decimal) that is a representation of years, months and days between dates (YYYYMMDD.)

    4) TIMESTAMPDIFF returns an approximation

    Examples I just ran:

    DB:LCAD->select days(current date) - days('08/01/2004') from sysibm.sysdummy1

    1
    -----------
    17

    1 record(s) selected.

    DB:LCAD->select current date - '08/01/2004' from sysibm.sysdummy1

    1
    ----------
    17.

    1 record(s) selected.

    DB:LCAD->select days(current date) - days('08/01/1994') from sysibm.sysdummy1

    1
    -----------
    3670

    1 record(s) selected.

    DB:LCAD->select current date - '08/01/1994' from sysibm.sysdummy1

    1
    ----------
    100017.

    1 record(s) selected.

    DB:LCAD->select timestampdiff(16,char(current date -'08/01/2004')) from sysibm.sysdummy1

    1
    -----------
    17

    1 record(s) selected.

    Read the SQL Reference manual on date, time, and timestamp arithmatic.

    HTH

    Andy

  3. #3
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    Sorry to bring up the subject. I was missing the fact that a lot of the data was from year 2003
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Maybe if you would post the results you are getting. I may be able to help you more.

    Andy

  5. #5
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    No. I mean the issue is resolved with the high figures. I was expecting to compare 2004 dates with current date, but a lot of the data was from 2003. And hence the high values.....
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

Posting Permissions

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