DB2 Timestamp select statement

Question

I am trying to run a simple query which gets me data based on timestamp  as follows   SELECT    FROM  lt table name gt   WHERE id   1  AND usagetime   timestamp  2012-09-03 08 03 06    WITH UR    This does not seem to return a record to me  whereas this record is present in the database for id   1   What am I doing wrong here   The datatype of the column usagetime is correct  set to timestamp

User · Accepted Answer

bhamby is correct  By leaving the microseconds off of your timestamp value  your query would only match on a usagetime of 2012-09-03 08 03 06 000000  If you don t have the complete timestamp value captured from a previous query  you can specify a ranged predicate that will match on any microsecond value for that time      WHERE id   1 AND usagetime BETWEEN  2012-09-03 08 03 06  AND  2012-09-03 08 03 07    or      WHERE id   1 AND usagetime  gt    2012-09-03 08 03 06      AND usagetime  lt   2012-09-03 08 03 07

User · Answer

You might want to use TRUNC function on your column when comparing with string format  so it compares only till seconds  not milliseconds   SELECT   FROM  lt table name gt  WHERE id   1  AND TRUNC usagetime   SS      2012-09-03 08 03 06     If you wanted to truncate upto minutes  hours  etc  that is also possible  just use appropriate notation instead of  SS    hour   HH    minute  MI    year  YEAR  or  YYYY    month  MONTH  or  MM    Day   DD

[db2] DB2 Timestamp select statement

Examples related to db2

Examples related to timestamp