CODEWITHSUNDEEP
sqlsql-serversql-server-2008
mosquito87
mosquito87

Reputation: 4440

SQL case: Return case value

The following is not working:

SELECT ..., 
    CASE Foo.Date
        WHEN '01.01.1900' THEN ''
        ELSE Foo.Date
END
FROM BAR, ...

I'd like to return the date if it's value is not 01.01.1900 (the fields type is DateTime). Is there any possibility to do so?

Upvotes: 1

Views: 9729

Answers (3)

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

SELECT ..., 
    CASE 
        WHEN CAST(Foo.Date AS DATE) = '01.01.1900' THEN ''
        ELSE CAST(Foo.Date AS VARCHAR(25))
    END AS Date
FROM BAR, ...

Since your field is DATETIME, it's value will always contains TIME part, and will only be equal to exact date if it's time part is 0:00 (midnight). Casting it to DATE will remove the time part and you can compare it.

Also returning '' and date in the same CASE will implicitly convert your empty string to date - which is 01.01.1900 again, so you need to convert date to varchar in other part of CASE, or return NULL instead of '' like in Adam Plocher's examples.

Upvotes: 0

Adam Plocher
Adam Plocher

Reputation: 14233

If casting it as a varchar isn't a problem, you can do something like:

SELECT ..., 
   CASE Foo.Date
        WHEN '01.01.1900' THEN ''
        ELSE cast(Foo.Date as varchar(50))
END as Date
FROM BAR, ...

Otherwise if it must stay a date, you'll need to use NULL instead of an empty string:

SELECT ..., 
   CASE Foo.Date
        WHEN '01.01.1900' THEN null
        ELSE Foo.Date
END as Date
FROM BAR, ...

Upvotes: 3

Shahid Iqbal
Shahid Iqbal

Reputation: 2135

Try this...

 SELECT ..., 
        (CASE WHEN Foo.Date!='01.01.1900' THEN Foo.Date 
        END) as Date
    FROM BAR, ...

Upvotes: 0

Related Questions