InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 16:33:33 GMT20http://www.sqlservercentral.com/Forums/Topic1408410-392-1.aspxYep, Yep. :-DTue, 22 Jan 2013 04:25:22 GMTm mcdonaldhttp://www.sqlservercentral.com/Forums/Topic1408410-392-1.aspxYou need to do something like that in the WHERE clause:select col1 ,CAST(col2 AS datetime) as col2testfrom #DateTestwhere isdate(col2) = 1Mon, 21 Jan 2013 15:46:15 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1408410-392-1.aspx[quote][b]drew.allen (1/21/2013)[/b][hr][quote][b]ScottPletcher (1/21/2013)[/b][hr]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?![/quote]In the CASE expression, the WHEN clause outputs a DATETIME value, but the ELSE clause outputs a VARCHAR value.Drew[/quote]D'OH, I didn't scroll down in the code.Mon, 21 Jan 2013 15:43:57 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1408410-392-1.aspx[quote]case isdate(col2) when 1 then cast(col2 as datetime) else col2 end as col2test[/quote]There is the culprit.Mon, 21 Jan 2013 15:35:14 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1408410-392-1.aspx[quote][b]ScottPletcher (1/21/2013)[/b][hr]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?![/quote]In the CASE expression, the WHEN clause outputs a DATETIME value, but the ELSE clause outputs a VARCHAR value.DrewMon, 21 Jan 2013 14:51:02 GMTdrew.allenhttp://www.sqlservercentral.com/Forums/Topic1408410-392-1.aspxHuh? 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?!Mon, 21 Jan 2013 14:46:05 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1408410-392-1.aspxI figured I was missing something/not thinking straight this morning.ThanksThu, 17 Jan 2013 07:44:02 GMTm mcdonaldhttp://www.sqlservercentral.com/Forums/Topic1408410-392-1.aspxYou 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 castcast(cast(col2 as datetime) as varchar)Thu, 17 Jan 2013 07:20:36 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1408410-392-1.aspxI am trying to understand why the case statement is failing...[code="sql"]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 DateCheckfrom #DateTestselect col2 ,case col1 when 'Order3' then 'BINGO' else col1 end as col1Testfrom #DateTestselect col1 ,case isdate(col2) when 1 then cast(col2 as datetime) else col2 end as col2testfrom #DateTestdrop table #DateTest[/code]Msg 242, Level 16, State 3, Line 16The 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?ThanksThu, 17 Jan 2013 07:13:30 GMTm mcdonald