Date error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Date error

Author

Topic

marcusn25
Yak Posting Veteran

56 Posts

Posted - 2013-09-03 : 16:48:20

Select
CASE
WHEN q.Q_DriversAssigned LIKE '%st%' THEN CAST(REPLACE(q.Q_RemovalCharge,'st ','') as DateTime)
End As Date
From
dbo.TblQuotation AS q

Hi. I am getting the following error from the above query, please assist.

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.
Warning: Null value is eliminated by an aggregate or other SET operation

Thanks in advance.

M. Ncube

M. Ncube

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-03 : 17:14:02

First, check if you really need that space in the REPLACE function. I would think it should be one of these:


WHEN q.Q_DriversAssigned LIKE '%st %' THEN CAST(REPLACE(q.Q_RemovalCharge,'st ','') as DateTime)
WHEN q.Q_DriversAssigned LIKE '%st%' THEN CAST(REPLACE(q.Q_RemovalCharge,'st','') as DateTime)

If that is not the issue then there are some strings in the Q_RemovalCharge column that are not in the proper format that allows SQL Server to convert them to datetime data type. You can get an idea of what those are by running this query:

SELECT REPLACE(q.Q_RemovalCharge,'st ','')
FROM o.TblQuotation AS q
WHERE q.Q_DriversAssigned LIKE '%st%'
AND ISDATE(REPLACE(q.Q_RemovalCharge,'st ',''))=0

marcusn25
Yak Posting Veteran

56 Posts

Posted - 2013-09-04 : 14:22:51

Thank you.I found the error. The code was failing when trying to retrieve 'August' dates from 'Q_DriversAssigned' becuase of (LIKE '%st%). I added the LEFT function to overcome this.

LEFT (q.Q_DriversAssigned, '4') LIKE '%st%' THEN CAST(REPLACE(q.Q_RemovalCharge,'st ','') as DateTime)

M. Ncube

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources