SQL USING CAST in a query inside a stored Procedure

Question

I was trying to use a query inside an Stored procedure which has a cast funtion. But when executing the SP I'm getting an error

Conversion failed when converting date and/or time from character string.

SELECT * 
 FROM TEMPTABLE 
 WHERE Status = 'A' 
 AND technician = 5 
 and schid not in (1047) 
 and CAST(approxstartdate as datetime) > = 
 ( select CAST(approxstartdate as datetime) 
 from trscheduler where Status = 'A' 
 AND technician = 5 
 and schid = 1059
 ) 
 order by CAST(approxstartdate as datetime) 

This query is used in a cursor.

Kindly let me know an alternative for this.

• Michael Kutz: Please tag your post with the database you are using. Each one seems to have a different syntax for converting strings to dates.
• Philᵀᴹ: Your data is bad, basically

Answer 1

You have data in approxstartdate column that cannot be converted to datetime, it’s bad data, chances are if you saw what that data is you wouldn’t know how to convert it to a datetime either.

If you are using SQL Server, take a look at try_convert which returns null if the value cannot be converted. You can use this function to find the values in your table that cannot be converted and then fix them or remove them.

Answer 2

The problem here is either bad data or incorrect date formats being used.

Assuming you're using MSSQL, as you've not stated what RDBMS, use the below code to identify the records that cannot be cast to a DATETIME:

SELECT * 
FROM 
(
 SELECT *, TRY_CAST(approxstartdate AS DATETIME) as cast_approxstartdate
 FROM TEMPTABLE 
) src
WHERE src.cast_approxstartdate IS NULL

Once you've identified them, you can either clean-up the bad data or identify another solution, for example, you may need to adjust the DATEFORMAT setting for the session or the database's application may be inputting data in an incorrect format depending on the regional settings of the client machine.