Results 1 to 4 of 4

Thread: Date Difference

  1. #1
    Join Date
    Sep 2009
    Posts
    3

    Unanswered: Date Difference

    Hi,

    I'm using the following to calculate the number of days between 2 dates:
    Code:
    DAYS(curdate()) - DAYS ("2009-07-01")
    The date format of my hardcoded string is yyyy-mm-dd. The return format of curdate() is also currently yyyy-mm-dd because this is the default on the DB2 server. But what if that default is changed and the returned format of curdate() is changed to something else like, mm/dd/yyyy? Will my function still work? How does DB2 know the format to use when it's not even specified? When calculating the difference of days in my above code, can (and how does) DB2 distinguish between yyyy-mm-dd and yyyy-dd-mm?

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    JimmyTwo, the format of the date is immaterial to DB2. It is just a display format. DB2 stores the actual date in internal format using 4 bytes. So 2009-09-07 (or 09/07/2009) is stored as:
    Code:
    2000
    0997
    You can prove this with days(date('2009-09-07')) - days(date('2008-01-20')).

    Change the formats of the dates to any valid DB2 format and you will get the same result.

    PS I don't know that curdate is a DB2 function. I have seen it in the SQL Server / Access world but never in DB2. What Edition / Version of DB2 are you using it in? Or are you using access as a front end to DB2?

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by JimmyTwo
    When calculating the difference of days in my above code, can (and how does) DB2 distinguish between yyyy-mm-dd and yyyy-dd-mm?
    DB2 will accept the ISO format YYYY-MM-DD or the format defined by territory code (USA, EUR, JIS) of the client. The format of YYYY-DD-MM is not a valid format for any of the above territory codes, so DB2 can figure it which one you are using. It is always best to use ISO format, so you don't have to worry about what territory code you happen to be using.

    As mentioned by Stealth_DBA the date is always stored internally within DB2 as YYYYMMDD in a format similar to mainframe packed decimal without the signed last half byte, so only 4 bytes are needed.

    For the current data, use current_date (although 'current date' also works).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Sep 2009
    Posts
    3
    Thanks for the help!

Posting Permissions

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