Date format issue when connecting to DB2 database

[Adorable09]

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';

or 

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.

QlikView 

[hic]

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' ; 

[Adorable09 · Accepted solution]

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