MSSQL Server 2008 Conversion failed when converting date and/or time from character string Error

Question

I am currently receiving the MSSQL Server 2008 error: Conversion failed when converting date and/or time from character string.

I've checked the following threads but am still having no lucks:

Conversion failed when converting date and/or time from character string while inserting datetime Conversion failed when converting date and/or time from character string in SQL SERVER 2008

Here is the query (I'm using PHP's PDO SQLSRV):

SELECT ISNULL(MYDATE,'None') AS MYDATE, 
ISNULL(CAST(rnm AS NVARCHAR(50)),'None') AS Rnm, 
DATENAME(dw, MYDATE) as nameOfDay, 
FROM tab1
INNER JOIN rnm ON (tab1.rte = rnm.rte)
WHERE DATEDIFF(S, '1970-01-01', MYDATE) >= :startDate
AND DATEDIFF(S, '1970-01-01', MYDATE) <= :endDate
GROUP BY MYDATE, CAST(rnm AS NVARCHAR(50)) WITH ROLLUP

The MYDATE field is of the type datetime in MSSQL. I have tried casting each of the MYDATE variables and the 1970-01-01 all as datetime, but I still receive the error.

The "rnm" is cast as VARCHAR because it's an old DB using the "text" type still, unfortunately.

Thanks.

---update:

$startDate = strtotime($_GET['startDate'] . " 00:00:00");
$endDate = strtotime($_GET['endDate'] . " 23:59:59");

The above is then bound to the prepared variable via bindParam().

have you tried '1970-01-01 00:00:00.0000000' instead of '1970-01-01'? — steven
And maybe this is the problem: ISNULL(MYDATE,'None') AS MYDATE. Maybe the error happens on trying to convert 'None' to a date. I am not sure if it uses MYDATE always from db or from your local alias. — steven
steven hit the nail. The issue is the ISNULL(MYDATE, 'None'), change to ISNULL ( convert (varchar(10), MYDATE), 'None') and also in the GROUP BY clause also — Squirrel
or remove the ISNULL () on MYDATE. let it return NULL and handle that in your front end. — Squirrel
datediff() returns integer. You are comparing integer with date. Since : startDate and endDate are just datetime, why not juse WHERE MYDATE >= startDate — Squirrel

Ivan Starostin Ivan Starostin · Accepted Answer · 2016-02-13T09:22:23

You are grouping by MYDATE which is datetime instead of IsNull(MYDATE, 'None') which is varchar and sometimes stores 'None' as a value. All those DATEDIFFs and conversions inside WHERE clause will lead to performance issues. Try to remove DATEDIFFs and pass startdate/enddate in datetime format.

MSSQL Server 2008 Conversion failed when converting date and/or time from character string Error

1 Answers