DB2 LUW Timestamp differences in versions

db2db2-luwtimestamp

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:

DB2 LUW v.9.7.0.6 FIXPACK_NUM=6

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!

Best Answer

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.

Related Question