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

497

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 typedatetime in MSSQL. I have tried casting each of the MYDATE variables and the1970-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 viabindParam().

170

Answer

Solution:

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.

People are also looking for solutions to the problem: PHP - Encoding issue when saving to XML file using SimpleXml

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.