By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,778 Members | 1,074 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,778 IT Pros & Developers. It's quick & easy.

Default date format for a DB2 instance

P: n/a
Hi all,

In Oracle and SQL server I have a date/time of 01/01/2005 13.24.54. If
I do a select using the date field with a criteria of 01/01/2005 it
will find it just fine. DB2 requires the selection criteria to be in
the format 2005-01-01-13.24.54.000000 or it will not find it.

Is there a configuration parameter that can be set in the DB2 instance
or if there is a pre-SQL command
(like Oracles Alter Session Set NLS_DATE_FORMAT=....)? How would one go
about doing this?

Thanks in advance.

Henry

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Ian
dbztax wrote:
Hi all,

In Oracle and SQL server I have a date/time of 01/01/2005 13.24.54. If
I do a select using the date field with a criteria of 01/01/2005 it
will find it just fine. DB2 requires the selection criteria to be in
the format 2005-01-01-13.24.54.000000 or it will not find it.

Is there a configuration parameter that can be set in the DB2 instance
or if there is a pre-SQL command
(like Oracles Alter Session Set NLS_DATE_FORMAT=....)? How would one go
about doing this?


select ... from table where date(timestamp) = '01/01/2005'

Nov 12 '05 #2

P: n/a
dbztax wrote:
Hi all,

In Oracle and SQL server I have a date/time of 01/01/2005 13.24.54. If
I do a select using the date field with a criteria of 01/01/2005 it
will find it just fine. DB2 requires the selection criteria to be in
the format 2005-01-01-13.24.54.000000 or it will not find it.


The reason is that a timestamp is conceptually a different thing than a
date. How should DB2 know that you want to find all the timestamps at your
date and not a specific one? To make that explicity, convert the timestamp
to a date and then do the comparison: SELECT ... FROM ... WHERE
DATE(<your-timestamp>) = '01/01/2005'

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.