January 17, 2013 at 7:13 am
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
January 17, 2013 at 7:20 am
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)
January 17, 2013 at 7:44 am
I figured I was missing something/not thinking straight this morning.
Thanks
January 21, 2013 at 2:46 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
January 21, 2013 at 2:51 pm
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
January 21, 2013 at 3:35 pm
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 21, 2013 at 3:43 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
January 21, 2013 at 3:46 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
January 22, 2013 at 4:25 am
Yep, Yep. 😀
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply