i have a query:
select ID from tb where date_from
NOT IN ('0001-01-01','')
AND date_to IN ('0001-01-01','');

The query should return all the ID with date_from that is not 0001-01-01 or ' ' and date_to that is '0001-01-01' or ' '.
I got an error saying,
The syntax of the string representation of a datetime value is incorrect.. SQLCODE=-180, SQLSTATE=22007
I am using db2 for windows,version v9.7. please help me fix the query thanks

You may be trying to use '' as null. This works in Oracle, but doesn't work in DB2 unless maybe you have Oracle compatibility tuned on, or maybe they changed Db2 to allow it in 9.7.

Try

where date_from
NOT IN ('0001-01-01',null)
AND date_to IN ('0001-01-01',null);

you could try this instead:

AND (date_from <> '0001-01-01' or NOT(date_to IS NULL))
AND (date_to = '0001-01-01' or date_to IS NULL)

cheers

thank you marcus, i have tried the null but it only returns nothing

"or" should be "AND", like this...
AND (date_from <> '0001-01-01' AND NOT(date_from IS NULL))
AND (date_to = '0001-01-01' or date_to IS NULL)

If "date_from <> '0001-01-01'" is true, it implies date_from IS NOT NULL.
(If date_from is null, "date_from <> '0001-01-01'" is UNKNOWN.)
So, this will be enough.
AND date_from <> '0001-01-01'
AND (date_to = '0001-01-01' or date_to IS NULL)

i got it. thanks artemis and tonkuma..