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 @ 10:04 AM
Points: 1,424, Visits: 1,385
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: Yesterday @ 4:10 AM
Points: 5,221, Visits: 5,119
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 @ 10:04 AM
Points: 1,424, Visits: 1,385
I figured I was missing something/not thinking straight this morning.


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

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 2,268, Visits: 3,426
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
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:10 AM
Points: 13,230, Visits: 12,709

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
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 2,268, Visits: 3,426
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1409738
Posted Monday, January 21, 2013 3:46 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 2,268, Visits: 3,426
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
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 @ 10:04 AM
Points: 1,424, Visits: 1,385
Yep, Yep.
Post #1409924
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse