Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2004
    Posts
    65

    Unanswered: About Date format in DB2 9.1.

    Hi all:
    I have db2 9.1 Express edition on Windows XP SP2. (Level DB2 v9.1.200.98)
    My problem is that I work the dates in the "dd/mm/yyyy" format, which
    I have defined in Regional Settings of the O.S.
    DB2 has the format "mm/dd/yyyy", which gives me errors when
    the date of some data is for example "13/01/2007" (month 13 doesn't exist).
    My question is how can I change the date format in DB2 to match the one
    selected in Regional Settings of the O.S. Any idea?
    Thanks for your help..

  2. #2
    Join Date
    Jun 2006
    Posts
    471
    when you bind db2cli / db2ubind.lst you can specify dateformat (iso/eur...
    have a look at bind command and rebind both lst files
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  3. #3
    Join Date
    Jul 2004
    Posts
    65
    Thanks.
    I have done the change, but none of the formats is useful for me.
    (EUR, LOC, etc.). I need dd/mm/yyyy. Anyone knows how to do this change?.
    Thanks...

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Why do you need dd/mm/yyyy? That is really confusing because slashes usually indicate for the rest of the world that we are talking about a US date value, and there you have months first.

    If you really want to use that format (preferred would be ISO because you can actually sort that way easily), then you need to apply functions to convert from/to something that DB2 understands.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by stolze
    Why do you need dd/mm/yyyy? That is really confusing because slashes usually indicate for the rest of the world that we are talking about a US date value, and there you have months first.

    If you really want to use that format (preferred would be ISO because you can actually sort that way easily), then you need to apply functions to convert from/to something that DB2 understands.
    I don't understand this thing about "sorting" (as also mentioned in your post on the newsgroup). DB2 can order by the date regardless of the format that it is returned to the application program. Why would anyone sort the date inside of an application program? DB2 always stores the date internally as YYYYMMDD packed into 4 bytes.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by Marcus_A
    I don't understand this thing about "sorting" (as also mentioned in your post on the newsgroup). DB2 can order by the date regardless of the format that it is returned to the application program. Why would anyone sort the date inside of an application program? DB2 always stores the date internally as YYYYMMDD packed into 4 bytes.
    True, DB2 sorts dates correctly. The only thing that differs is the external representation. But I wasn't talking about DB2 specifically but rather date comparisons in general. I can imagine that pretty every application has to manage dates and has to compare dates. There, the ISO format is very well suited because a simple string comparison is sufficient. Other formats require more involved logic. (And going to the DBMS just to compare two date values is surely not the best move in many situations.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Aug 2004
    Posts
    4
    I have the same issue with DB2 9.1 Express on RHEL 5. I appreciate that it's not yet a supported combination of DB2 and OS, but I'll send a support request to IBM as I suspect it's a problem for anyone working in the UK. I haven't tried converting an existing V8 database yet - still testing with a new one.

    As for "Why do you need dd/mm/yyyy", well in the UK we just do and that's all there is to it.

    The support for this date format for Territory Code 44 is stil documented in the V9.1 manuals.

  8. #8
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by francis01
    I work the dates in the "dd/mm/yyyy" format, which
    I have defined in Regional Settings of the O.S.
    There are two independent things here:
    - The Regional Date/Time Settings, which determine how dates are displayed (as in SELECT datefield FROM mytable); and
    - The input format for date constants (as in ... WHERE datefield = '2007-03-27')

    For the second issue, there are only three formats:
    - 'yyyy-mm-dd' (the ISO format)
    - 'mm/dd/yyyy' (the USA format)
    - 'dd.mm.yyyy' (the EUR format)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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