I am trying to convert a MySQL query to SQL Server and this is as far as I have gotten. It appears SQL Server is unhappy with the fact that the table is storing DATE's (duedate in this case) as VARCHAR. So with that in mind, I tried converting them using CONVERT(DATE, EXPRESSION). Unfortunately I keep getting the "Conversion failed when converting date and/or time from character string" error message. I'm not sure how else to attempt a conversion.
SELECT MYSQL_locationstesting.*, MYSQL_locations.sitenumb
FROM MYSQL_locationstesting
INNER JOIN MYSQL_locations
ON MYSQL_locationstesting.siteid=MYSQL_locations.id
WHERE MYSQL_locationstesting.display = 1
AND (CONVERT(DATE, MYSQL_locationstesting.duedate) < CAST(GETDATE() AS DATE)
AND MYSQL_locations.active = 1
OR CONVERT(DATE, MYSQL_locationstesting.duedate) BETWEEN DATEADD(DAY, -1, CAST(GETDATE() AS DATE))
AND DATEADD(DAY, 5, CAST(GETDATE() AS DATE)))
Starting in SQL Server 2012, you have TRY_PARSE/CONVERT/CAST that can help. You are seeing that error because something is not able to be explicitly cast to a date type.
SELECT CONVERT(DATE, 'This will break!')
SELECT *
FROM your_table
WHERE TRY_PARSE(your_column AS DATE) IS NULL
The 2nd query will return all your values that are unable to be cast to a date type. So you can add that to the bottom of your query to filter them all out:
AND TRY_PARSE(your_column AS DATE) IS NOT NULL
This will remove them from your results, so be careful with it if you want to still capture those values somehow.