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

1

Hi Team,

Requesting your help here.
We have a tool, where we use SQL to make reports. I am getting invalid date string error, when I try to execute below shown query. Surprising element is that the moment I add another clause in 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 ?

image
image804×617 14.7 KB

2

Welcome

Is this against Microsoft sql server database?

3

SQL Server doesn't have a data type 'SQL_DATE'. With that said - I see several problems with this query:

  1. No need to query from a subquery. The subquery has columns that are not included in the outer query, and even if those are to be included - unless you are using those columns in some type of calculation in the outer query there isn't a reason to do that.
  2. You are trying to create a SQL_DATE from a column named 'date_time'. Since you are 'parsing' that I assume it is a string and not an actual date and time.
  3. Parsing the string date_time and trying to convert to an actual date - then filtering that value by comparing to sysdate (current date).

It is most likely a problem in that column. The data in that column cannot be parsed into a valid date - therefore you are getting an error. When you add the additional filtering, the bad row is excluded and you avoid the error.

If this were SQL Server - you could use TRY_CONVERT instead of CONVERT and return a NULL value. Those rows would then be excluded in the date filtering because they are null values.