topic Date format issue when connecting to DB2 database in App Development

Date format issue when connecting to DB2 database

Adorable09 — Fri, 15 Nov 2024 20:53:10 GMT

Hi All,

I am connecting to DB2 Server and applying a simple date filter in where condition as shown below

SQL SELECT *
FROM XYZ where TDATE='22-08-2024';

FROM XYZ where TDATE='$(vDate)';

TDATE is in 'DD-MM-YYYY' format in the database so is vDate variable.

But I get an error :

QVX_UNEXPECTED_END_OF_DATA: SQL##f - SqlState: 22007, ErrorCode: 4294967115, ErrorMsg: [IBM][CLI Driver][DB2/LINUXX8664] SQL0181N
The string representation of a datetime value is out of range. SQLSTATE=22007

This seems to be issue related to DB2.Can anyone please suggest?

Thanks.

Re: Date format issue when connecting to DB2 database

hic — Tue, 27 Aug 2024 08:34:30 GMT

Where-clauses with dates in databases can be tricky, depending on how the dates are stored. Often you need to use Cast() or some formatting function. Try

WHERE VARCHAR_FORMAT (TDATE,'YYYY-MM-DD') = '2024-08-22' ;

Re: Date format issue when connecting to DB2 database

Adorable09 — Tue, 27 Aug 2024 11:08:09 GMT

@hic Thanks it works .Looks like TDATE accepts YYYY-MM-DD OR M/DD/YYYY format.