timestamp comparison 1

[cisco999]

New to DB2. What is the correct DB2 SQL syntax for the following timestamp comparison?

select *
from ZX05A.PARSED_PRODUCT_DTL where parsed_product_id = 4 and
start_tmestp = '2007-10-16 16:07:50.060167'

start_tmestp has a data type of timestamp. Thanks very much!

 

[fredericofonseca]

easiest way for you to know it, is to do a select in any of the system or your own tables that already has a timestamp. look at the output and you will know what is the format.

Reading the freely available IBM DB2 manuals also helps.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[MarcLodge]

cisco999,

SELECT CURRENT_TIMESTAMP
FROM SYSIBM.SYSDUMMY1

will give you the format of a timestamp.

Marc

 

[cisco999]

Should have been more clear. I know the format, it's the syntax to compare timestamps which eludes me. The SQL statement generates the following message.
Error: SQL0181N The string representation of a datetime value is out of range. SQLSTATE=22007
(State:22007, Native Code: FFFFFF4B)

 

[MarcLodge]

Cicso999,
If you are using the value of the timestamp that you have used in your example i.ie '2007-10-16 16:07:50.060167' then this is incorrect.

Try '2007-10-16-16.07.50.060167' instead.

Marc

 

[sathyarams]

I guess the date, time and timestamp output might depend on the locale/region of the database

I may be wrong though ..

To avoid confusions, I always recommend using ISO formats in queries.

HTH
Sathyaram

cisco999,

SELECT CURRENT_TIMESTAMP
FROM SYSIBM.SYSDUMMY1

will give you the format of a timestamp.

Marc

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

&

http://www.idug.org/user/UserLogin.asp

 

[cisco999]

'2007-10-16 16:07:50.060167' is the format copied while browsing the data. Adding a "-" between the date and time resulted in the same error.

 

[cisco999]

I'm using the WinSQL tool and apparently it displays timestamps differently than how they are actually stored. There can't be a space between the two 16's and the colons need to be dots so the correct format is '2007-10-16-16.07.50.060167' not '2007-10-16 16:07:50.060167'.

 

[MarcLodge]

You mean like what it said in my last post then?

Delighted that you managed to get it resolved.

Marc

 

[cisco999]

My bad, I didn't notice the change of the colons to periods in your previous post. Thanks for your help Marc!