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..

when you bind db2cli / db2ubind.lst you can specify dateformat (iso/eur...
have a look at bind command and rebind both lst files

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...

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.

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.)

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.

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)