Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Trying to understand ISDATE & CAST behavior Expand / Collapse
Author
Message
Posted Thursday, January 17, 2013 7:13 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:56 AM
Points: 1,299, Visits: 1,274
I am trying to understand why the case statement is failing...


create table #DateTest
(col1 varchar(55),
col2 varchar(255)
)


insert into #DateTest(col1, col2)
values ('Order1', '05/12/07'),('Order2', '11/5/2008.'),('Order3', 'Some text'),('Order4', 'Sold 5/5/2009'),('Order5', '8/8/08'),('Order6', '7/4/1776')

select col1
,col2
,isdate(col2) as DateCheck
from #DateTest


select
col2
,case col1
when 'Order3' then 'BINGO'
else col1
end as col1Test
from #DateTest


select
col1
,case isdate(col2)
when 1 then cast(col2 as datetime)
else col2
end as col2test
from #DateTest


drop table #DateTest


Msg 242, Level 16, State 3, Line 16
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.


Why is it trying to do the conversion and not just going to the else?

Thanks
Post #1408410
Posted Thursday, January 17, 2013 7:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 5,131, Visits: 4,918
You can't have two different data types in the same column, your effectivly saying I want DATETIME and VARCHAR data in the same column which is not allowed.

You will need to do a double cast

cast(cast(col2 as datetime) as varchar)




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1408417
Posted Thursday, January 17, 2013 7:44 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:56 AM
Points: 1,299, Visits: 1,274
I figured I was missing something/not thinking straight this morning.


Thanks
Post #1408426
Posted Monday, January 21, 2013 2:46 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:57 AM
Points: 1,946, Visits: 2,878
Huh? I don't see anywhere a "datetime" data type is specified, only varchar. I don't see why the code couldn't run fine as is.

I guess your actual data type is different than shown in your sample code?!


SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1409719
Posted Monday, January 21, 2013 2:51 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 8:24 AM
Points: 1,240, Visits: 5,421
ScottPletcher (1/21/2013)
Huh? I don't see anywhere a "datetime" data type is specified, only varchar. I don't see why the code couldn't run fine as is.

I guess your actual data type is different than shown in your sample code?!


In the CASE expression, the WHEN clause outputs a DATETIME value, but the ELSE clause outputs a VARCHAR value.

Drew


J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Post #1409721
Posted Monday, January 21, 2013 3:35 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 13,059, Visits: 11,887

case isdate(col2)
when 1 then cast(col2 as datetime)
else col2
end as col2test


There is the culprit.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1409734
Posted Monday, January 21, 2013 3:43 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:57 AM
Points: 1,946, Visits: 2,878
drew.allen (1/21/2013)
ScottPletcher (1/21/2013)
Huh? I don't see anywhere a "datetime" data type is specified, only varchar. I don't see why the code couldn't run fine as is.

I guess your actual data type is different than shown in your sample code?!


In the CASE expression, the WHEN clause outputs a DATETIME value, but the ELSE clause outputs a VARCHAR value.

Drew



D'OH, I didn't scroll down in the code.


SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1409738
Posted Monday, January 21, 2013 3:46 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:57 AM
Points: 1,946, Visits: 2,878
You need to do something like that in the WHERE clause:


select
col1
,CAST(col2 AS datetime) as col2test
from #DateTest
where isdate(col2) = 1


SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1409740
Posted Tuesday, January 22, 2013 4:25 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:56 AM
Points: 1,299, Visits: 1,274
Yep, Yep.
Post #1409924
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse