Error HY000:[DataDirect][OpenEdge JDBC Driver][OpenEdge] Invalid date string (7497)

[Mady]

Hi All,

Requesting your help here.
We have a tool, where we use Progress SQL to make reports. I am getting an invalid date string error, when I try to execute below-shown query. The surprising element is that the moment I add another clause in the outer WHERE condition i.e. "*wh_num = 50" - I do not get this error and I see the result successfully. Can you please help in identifying the issue here, so that I can run the below-shown query without any issue? Thanks.

 

[Mady]

Requesting inputs here. Please suggest. Thanks.

 

[Stefan]

I would guess that this is your convert function trying to cast some text string in date_time to a date.
Your assumption that all records contain a value that can be cast to a date seems untrue.
Further qualifying your select statement is resulting in a record with an invalid date not being part of the selection, so no attempt is made to cast it to a date.

Now, how to find which record(s) have a date_time field that cannot be cast to a date... I do not know how much data you have, but you may be able to spot the bad dates visually by stripping the convert function.