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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
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