Conversion failed when converting date and/or time

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)

Conversion failed when converting date and/or time

Author

Topic

blimonov
Starting Member

1 Post

Posted - 2009-10-14 : 20:33:27

I get the following message when I try to execute my SQL statement bellow

"Msg 241 Conversion failed when converting date and/or time from character string."

select
case when MyDate = '1900-01-01 00:01:00.000' then 'NA'
else MyDate end
from MyTable

As you can see I'm not converting any data
MyDate(datetime,null)

The following statement works fine but I don't want to convert MyDate

select
case when MyDate = '1900-01-01 00:01:00.000' then 'NA'
else convert(varchar,MyDate,101) end
from MyTable

I have tried "CAST" but still no luck
Any help is greatly appreciated

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2009-10-14 : 21:47:18

The resulting values form your SELECT for that column seem to have 2 datatypes - varchar if its 'NA', date if its a regular value. This is why you are seeing the error. You need to convert into varchar datatype which can take both values...

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-15 : 02:57:37

or use NULL in place of 'NA' and let the front end show it as 'NA'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources