0

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().

Community
  • 1
  • 1
Shackrock
  • 4,601
  • 10
  • 48
  • 74
  • have you tried '1970-01-01 00:00:00.0000000' instead of '1970-01-01'? – steven Feb 12 '16 at 22:32
  • what is ;startDate and :endDate ? date time ? Note that datedfif() returns an integer – Squirrel Feb 12 '16 at 22:33
  • 3
    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 Feb 12 '16 at 22:38
  • Added some additional in the question for you @Squirrel. steven, hmmm, that could be the problem - how can I force MSSQL to take the DB values again if it was trying to do that? – Shackrock Feb 12 '16 at 22:43
  • 1
    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 Feb 12 '16 at 22:48
  • 1
    or remove the ISNULL () on MYDATE. let it return NULL and handle that in your front end. – Squirrel Feb 12 '16 at 22:51
  • @Squirrel well that did stop the error, but I have no data being returned after fetch, so I have bigger problems on my hand =). Thanks. – Shackrock Feb 12 '16 at 22:58
  • 1
    datediff() returns integer. You are comparing integer with date. Since : startDate and endDate are just datetime, why not juse WHERE MYDATE >= startDate – Squirrel Feb 13 '16 at 01:06
  • @Squirrel The strtotime produces an integer as well as a unix timestamp – Shackrock Feb 13 '16 at 14:41

1 Answers1

0

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.

Ivan Starostin
  • 8,798
  • 5
  • 21
  • 39