Im new to DB2, my back ground is in MS SQL server 2000 thru 2012. I have a developer using

Local DB DB2 LUW v9.7.500.4299 FIXPACK_NUM = 5

She is selecting some data from a table where the field is a Timestamp data type. She can select it and get a return using the following WHERE CRTD_DT_TM = '2014-11-19 18:00:00'

But if she does the same thing in the Prod version:


she doesn't get anything in return. She actually has to add the milliseconds to the query:

Where CRTD_DT_TM = '2015-01-13 04:32:13.080000'

Is this an issue between versions? If so Is there a FIX pack that has addressed this?

Thanks in advanced!

  • 1
    Well, the TIMESTAMP data type includes fractional seconds. If you don't specify them, as in '2014-11-19 18:00:00' they default to zeroes. However, if the actual value in the table does have fractional seconds, comparing '2015-01-13 04:32:13', which expands to '2015-01-13 04:32:13.000000', with '2015-01-13 04:32:13.080000' will obviously return false.
    – mustaccio
    Commented Jan 26, 2015 at 16:02

1 Answer 1


Depending on its precision, a TIMESTAMP value may have fractional seconds. For literals, the precision is defined by the presence of a fractional part after seconds, as well as the number of digits in the fractional part. For columns, the precision is defined as an optional parameter after the name of the type in the column definition. For instance:

SomeColumn TIMESTAMP(9)

defines the column as a timestamp with nanosecond precision (9 digits after the decimal point). (As you have SQL Server background, you can compare this to the SQL Server datetime2 type, which also has optional precision.)

For reference, see IBM Knowledge Center - Datetime values.

It looks likely that the column in question is defined differently between the development environment and production, the development definition being TIMESTAMP(0) (no fractional seconds) and the production definition TIMESTAMP(6) (or simply TIMESTAMP, which means the same).

That explains why your developer never has to specify fractions of a second in the development environment – because the column's values cannot possibly have fractional seconds. At the same time, she may have to specify timestamps with a fractional part in the production environment because the column type there allows fractions of a second in the values and some timestamps apparently do have fractional parts.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.