SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Trying to understand ISDATE & CAST behavior


Trying to understand ISDATE & CAST behavior

Author
Message
m mcdonald
m mcdonald
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2007 Visits: 1692
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
anthony.green
anthony.green
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23728 Visits: 6519
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


m mcdonald
m mcdonald
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2007 Visits: 1692
I figured I was missing something/not thinking straight this morning.


Thanks
ScottPletcher
ScottPletcher
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19153 Visits: 7410
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
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.
drew.allen
drew.allen
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15149 Visits: 11199
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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60969 Visits: 17954

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 Modens 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)
ScottPletcher
ScottPletcher
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19153 Visits: 7410
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
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.
ScottPletcher
ScottPletcher
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19153 Visits: 7410
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
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.
m mcdonald
m mcdonald
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2007 Visits: 1692
Yep, Yep. :-D
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search