Strange behaviour for db2 query for timestamp data type
database, db2
Solution
No, DB2 stores the full value of the timestamp, including the fractional seconds. You may wish to change the format the system displays timestamps in to something that includes milliseconds.
Try using this instead:
SELECT *
FROM Sample
WHERE lastModifiedDate >= TIMESTAMP('2012-04-03 07:59:50')
AND lastModifiedDate < TIMESTAMP('2012-04-03 07:59:50)' + 1 SECONDS
Unless you have the full value of the timestamp, including milliseconds, you're going to be getting a range - when accessing a range of data, use 'lower-bound inclusive, upper-bound exclusive'.
Problem
I want to query for timestamp data type in db2. I wrote query below `Select * from sample where LASTMODIFIEDDATE = timestamp('2012-04-03 07:59:50')` I didn't get any result for above query , then I tried `Select * from sample where LASTMODIFIEDDATE > timestamp('2012-04-03 07:59:50')` In above query I got results matching timestamp '2012-04-03 07:59:50' plus for greater values of timestamp, e.g '2012-04-03 08:59:50'. If I am getting results for '>' operator then why not I am not getting any results for '=' operator ? Any reasons or am I writing wrong query ? Thanks !