SQL 2008 CASE statement aggravation...

Question

Why does this fail:

DECLARE @DATE VARCHAR(50) = 'dasf'
SELECT CASE WHEN ISDATE(@DATE) = 1 THEN CONVERT(date,@DATE) ELSE @DATE END

Msg 241, Level 16, State 1, Line 2 Conversion failed when converting date and/or time from character string.

Why is it trying to convert dasf to date when it clearly causes ISDATE(@DATE) = 1 to evaluate to false...

If I do:

SELECT ISDATE(@DATE)

The return value is 0.

Answer 1

CASE returns a single type. In this case, the type is Date, found from your THEN clause. It is implicitly converting the ELSE clause result to Date to match.

You must choose a single type to be returned by CASE. It cannot be used to return sometimes Date and sometimes varchar.

from MSDN: http://msdn.microsoft.com/en-us/library/ms181765.aspx

Return Types

Returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression. For more information, see Data Type Precedence (Transact-SQL).

and then following that link: http://msdn.microsoft.com/en-us/library/ms190309.aspx

8) date

27) varchar

---

It's not clear what you want, so it's hard to offer alternatives (I don't know if the CASE is part of a larger query or script), but here's a couple things you can do:

-- choose a single return type per CASE expression
SELECT
  CASE
    WHEN IsDate(@Date) = 1
    THEN convert(date, @Date)
    ELSE null
  END as [Date],
  CASE
    WHEN IsDate(@Date) = 1
    THEN null
    ELSE @Date
  END as [VarChar]

--use control flow to select what you want.
IF IsDate(@Date) = 1
THEN
  SELECT convert(date, @Date)
ELSE
  SELECT @Date

Answer 2

try this:

DECLARE @DATE VARCHAR(50) = 'dasf'
SELECT CASE 
           WHEN ISDATE(@DATE)=1 THEN CONVERT(char(23),CONVERT(date,@DATE),121) 
           ELSE @DATE
       END

It will basically format your valid date and leave the non-dates alone. Is that what you are after?

actual working sample:

DECLARE @YourTable table (DATE VARCHAR(50))
INSERT @YourTable VALUES ('dasf')
INSERT @YourTable VALUES ('1/1/2010')

SELECT
    CASE 
        WHEN ISDATE(DATE)=1 THEN CONVERT(char(23),CONVERT(datetime,DATE),121) 
        ELSE DATE
    END AS DATE
    FROM @YourTable

OUTPUT:

DATE
--------------------------------------------------
dasf
2010-01-01 00:00:00.000

(2 row(s) affected)

In the working example, I made a substitute from date data type to datetime because I'm on SQL Server 2005 and date datatype is SQL Server 2008 only.