DB2 FOR LUW / DB2FORLUW / A50 - DB2

DB2 might produce incorrect result when executing a query with 
datetime predicates. 
The incorrect result will only happen if the table has a 
timestamp of the form 'YYYY-DD-MM:HH:MM:SS:24.00.00.000000' 
 
 
db2 "create table tablexyz (ts TIMESTAMP DEFAULT 
'2999-12-31-24.00.00.000000')" 
 
db2 "insert into tablexyz values ('2999-12-31-24.00.00.000000')" 
 
 
db2 "select * from tablexyz" 
 
TS 
-------------------------- 
2999-12-31-24.00.00.000000 
 
  1 record(s) selected. 
 
Example 1: 
db2 "select * from tablexyz where date(ts) = '2999-12-31'" 
 
ts 
-------------------------- 
 
  0 record(s) selected. 
 
Example 2: 
db2 "select * from tablexyz where year(ts) = 2999" 
 
TS 
-------------------------- 
 
  0 record(s) selected. 
 
Example 3: 
db2 "select * from tablexyz where year(ts) = 2999 and month(ts) 
= 12" 
 
TS 
-------------------------- 
 
  0 record(s) selected.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 10.5 and Fix Pack 3                   * 
****************************************************************

Workaround: Manually rewrite the predicate from date/year to 
timestamp format. 
Example1. 
select * from tablexyz where ts >= 
'2999-12-31-00.00.00.000000' and ts <= 
'2999-12-31-24.00.00.000000' 
 
example 2. 
select * from tablexyz where ts >= 
'2999-01-01-00.00.00.000000' and ts <= 
'2999-12-31-24.00.00.000000' 
 
select * from tablexyz where ts >= 
'2999-12-01-00.00.00.000000' and ts <= 
'2999-12-31-24.00.00.000000'

DB2 Version 10.5 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 10.5 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Cancun Release 10.5.0.4 (also known as Fix Pack 4) for Linux, UNIX, and Windows
 DB2 Version 10.5 Fix Pack 9 for Linux, UNIX, and Windows
 

Problem was first fixed in DB2 Version 10.5 and Fix Pack 3

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

27.08.2013
27.02.2014
27.02.2014