country code

[webrod]

Hi,

I'm new to DB2.
Is there a way to know the date format of the DB2 instance from a SQL
query?
I guess the date format or the country code is stored in a system
table?
which one?

thanks

Rod

[JvdB]

webrod wrote:

Hi,

I'm new to DB2.
Is there a way to know the date format of the DB2 instance from a SQL
query?
I guess the date format or the country code is stored in a system
table?
which one?

thanks

Rod

Hi Rod,

you might want to read these docs:

- Supported territory codes and code pages:
http://publib.boulder.ibm.com/infoce...n/r0004565.htm

- Date and time formats by territory code:
http://publib.boulder.ibm.com/infoce...n/r0004572.htm

and this article "Fun with Dates and Times" on the DeveloperWorks site:
http://www-128.ibm.com/developerwork.../0211yip3.html

HTH.

--
Jeroen

[Mark A]

webrod wrote:

Hi,

I'm new to DB2.
Is there a way to know the date format of the DB2 instance from a SQL
query?
I guess the date format or the country code is stored in a system
table?
which one?

thanks

Rod

DB2 always stores the date in the same internal format. The way it is
displayed depends on the terrritory code of the client that submits the
SQL Select, but it can be overriden by using the CHAR funciton in the
select statement.

select current_date from sysibm.sysdummy 1
01/12/2007

select CHAR(current_da te, ISO) from sysibm.sysdummy 1
2007-01-12

[webrod]

DB2 always stores the date in the same internal format. The way it is

displayed depends on the terrritory code of the client that submits the
SQL Select, but it can be overriden by using the CHAR funciton in the
select statement.

select current_date from sysibm.sysdummy 1
01/12/2007

select CHAR(current_da te, ISO) from sysibm.sysdummy 1
2007-01-12

thanks for your answer.
I agree but 2 questions:
- what is the internal format? I guess it is yyyymmdd
- how would you do the insert (SQL INSERT) to use the internal format
instead of the local format date? Is there a universal way to do an
insert with a DATE field?

Because when I do this:
select * from MYTABLE where DAT_SES = '20070115'
I get this error message:
General SQL error.
[IBM][CLI Driver][DB2/NT] SQL0180N "The syntax of the representation in
the form of chain of a value date/time is incorrect." SQLSTATE=22007

(I have translated from french, I hope this is correct. Not sure about
the word "chain" maybe STRING is a better translation).

Anyway I need to do this:
select * from MYTABLE where DAT_SES = '01/15/2007'
because, the server has the US format (mm/dd/yyyy).
I am running this query from a DB2 client from a workstation. This
workstation has the french configuration.
So the result is "15/01/2007" (french format is dd/mm/yyyy).

So, to conclude, the server format needs to be used (US forma in my
example), not the internal one nor the client one.
And the result (the display) is the local format (french format in my
configuration) .

The thing is that I do not know the server format. I mean not always.
So I don't see a solution. I don't see how to do my INSERT.
I undertsand how I can display the DATE field witht the CHAR function,
but I don't see a way to do an INSERT if I do not know the server
format which is required..

Could you help me?
Rod

[Mark A]

"webrod" <ro************ **@gmail.comwro te in message
news:11******** **************@ 11g2000cwr.goog legroups.com...

thanks for your answer.
I agree but 2 questions:
- what is the internal format? I guess it is yyyymmdd

The internal format is yyyymmdd packed into 4 bytes. (You don't need to
know.)

- how would you do the insert (SQL INSERT) to use the internal format
instead of the local format date? Is there a universal way to do an
insert with a DATE field?

DB2 always understands the ISO format 'YYYY-MM-DD' on an insert or predicate
comparison, regardless of client locale.

Because when I do this:
select * from MYTABLE where DAT_SES = '20070115'
I get this error message:
General SQL error.
[IBM][CLI Driver][DB2/NT] SQL0180N "The syntax of the representation in
the form of chain of a value date/time is incorrect." SQLSTATE=22007

where DAT_SES = '2007-01-15'

(I have translated from french, I hope this is correct. Not sure about
the word "chain" maybe STRING is a better translation).

Anyway I need to do this:
select * from MYTABLE where DAT_SES = '01/15/2007'
because, the server has the US format (mm/dd/yyyy).
I am running this query from a DB2 client from a workstation. This
workstation has the french configuration.
So the result is "15/01/2007" (french format is dd/mm/yyyy).

As mentioned above, use the ISO format avoid ambiguity:
where DAT_SES = '2007-01-15'

So, to conclude, the server format needs to be used (US forma in my
example), not the internal one nor the client one.

No, you can always use '2007-01-15' (this is the ISO format and you should
use it on all inserts and predicates if possible).

And the result (the display) is the local format (french format in my
configuration) .
The thing is that I do not know the server format. I mean not always.
So I don't see a solution. I don't see how to do my INSERT.

When doing inserts and in predicates, always use the ISO format:
where DAT_SES = '2007-01-15'
and DB2 will understand it

I undertsand how I can display the DATE field witht the CHAR function,
but I don't see a way to do an INSERT if I do not know the server
format which is required..

Could you help me?

Always use:
where DAT_SES = '2007-01-15' ('YYYY-MM-DD') in inserts and in predicates
and you do not have to worry about which locale the database or the client
is set to.

When you select data, you will have to worry about the client locale, but
that can be overriden by using

SELECT CHAR(DAT_SES, ISO) to always return the ISO format, or whatever
format you want, regardless of client settings.

[webrod]

thanks you very much for your answer!
You're the best!!

Rod