asked
Error when converting datetime from character string
I've googled this and plenty about this in the web but I am not sure why my string is not converting to datetime. Any ideas? SELECT gi.ITEM_NUM ,i.DSCR ,CAST(CASE WHEN (cast(MAX(CRTE_DTS) as datetime)) > DATEADD(d,-7,GETDATE()) THEN 1 ELSE 0 END AS BIT) AS RECENT_ADD FROM DAMART.dbo.T_x_ITEM AS gi INNER JOIN DB.dbo.T_ITEM AS i ON i.ITEM_NUM = gi.ITEM_NUM
Is that the only row in the table? If not, it's quite likely there is at least one non-date string value stored in the CRTE_DTS column. Try this to search for offending values: SELECT CRTE_DTS FROM DAMART.dbo.T_x_ITEM AS gi INNER JOIN DB.dbo.T_ITEM AS i ON i.ITEM_NUM = gi.ITEM_NUM WHERE ISDATE(CRTE_DTS) = 0
How is the date represented in the database? What's the result of just running `"SELECT MAX(CRTE_DTS) FROM... (wherever)"` I've just tried the following, and it's worked quite happily, so I'm reckoning there might be some extra data that's floating to the top of the "MAX" calculation... DECLARE @t VARCHAR(20) SELECT @t = 'Oct 4 2007 2:41PM' SELECT cast(@t as datetime) ... either that, or you've got some strange localisation settings.
