Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, January 20, 2016 8:37 AM
Points: 1,670, Visits: 1,629
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: Thursday, February 4, 2016 3:41 AM
Points: 5,965, Visits: 6,044
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
When a question, really isn't a question - Jeff Smith
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
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, January 20, 2016 8:37 AM
Points: 1,670, Visits: 1,629
I figured I was missing something/not thinking straight this morning.


Thanks
Post #1408426
Posted Monday, January 21, 2013 2:46 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:46 PM
Points: 3,443, Visits: 5,413
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)

"If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them." James Blackburn, in closing argument in the "Fatal Vision" murders trial
Post #1409719
Posted Monday, January 21, 2013 2:51 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:26 AM
Points: 1,581, Visits: 6,973
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


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 15,267, Visits: 15,256

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
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:46 PM
Points: 3,443, Visits: 5,413
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)

"If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them." James Blackburn, in closing argument in the "Fatal Vision" murders trial
Post #1409738
Posted Monday, January 21, 2013 3:46 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:46 PM
Points: 3,443, Visits: 5,413
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)

"If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them." James Blackburn, in closing argument in the "Fatal Vision" murders trial
Post #1409740
Posted Tuesday, January 22, 2013 4:25 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, January 20, 2016 8:37 AM
Points: 1,670, Visits: 1,629
Yep, Yep.
Post #1409924
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse