Troubleshooting
Problem
DB2 SQL that with a WHERE clause using a date string. i.e "2012-12-31" returns a different number of rows when it is ran from the Client's application compared to run from CLP
Cause
By default, the database creation bound the CLP with option DATETIME LOC that means that when dealing with dates converted to strings CLP uses the format associated with client’s locale. Because the Client application and the CLP are using different locales their results may be different
Example:
The Client Application is using ISO ('YYYY-MM-DD') and
CLP using USA (MM/DD/YYYY).
SQL:
SELECT CAST(date_col AS VARCHAR(20)) FROM testtab
WHERE CAST(date_col AS VARCHAR(20)) = '2012-12-31' ;
Under the given conditions, the above SQL may return rows when it is ran from the Client Application but it will never satisfies any condition when it is ran from CLP because the CLP will satisfy only date-string in the format of 'MM/DD/YYYY'.
Resolving The Problem
There are different options to solve this problem. Two possible options are:
1: Write a SQL that can be ran from any of the interface using the same date string format. You can accomplish that by using function VARCHAR_FORMAT to format the date to a specific date string format:
SELECT VARCHAR_FORMAT(DATE_col,'YYYY-MM-DD') AS VCHAR_FORMATED, date_col FROM testtab
WHERE VARCHAR_FORMAT (date_col,'YYYY-MM-DD') = '2013-04-11'
VCHAR_FORMATED DATE_COL
-------------------------------------------- ----------
2013-04-11 04/11/2013
1 record(s) selected.
2: Rebind the CLP display format to ISO (YYYY-MM-DD).
For detail on rebinding the CLP display format see; http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.admin.nls.doc/doc/r0004572.html
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21634264