SQL Server doesn't have a data type 'SQL_DATE'. With that said - I see several problems with this query:
- 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.
- 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.
- 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.