sql case statement with date values

sql, sql-server

Solution

I would first convert it to a null:

select
    case
        when h.sDestructionDate = '01/01/1991' then null
        else h.sDestructionDate
    end as sDestructionDate
from tblDocumentHeader h

Then look to your reporting tool to present a blank if the value is `null`

Mixing data with presentation is a mistake. This approach is simple and keeps the two concerns of data and rendering separate.

Problem

I have a date column in my database table. ``` SELECT sDestructionDate FROM tblDocumentHeader ``` below is the result of above query I need to print empty for the date value '1991-01-01' I expect a result set something like below. I tried the below query but it still print that '1991-01-01' date. ``` SELECT CASE CONVERT(date,h.sDestructionDate,103) WHEN '01/01/1991' THEN '' ELSE CONVERT(date,h.sDestructionDate,103) END AS 'sDestructionDate' FROM tblDocumentHeader h ``` I don't want to convert the result into varchar value. below query does gives me the result but i need to return it from date format. ``` SELECT CASE CONVERT(varchar,h.sDestructionDate,103) WHEN '01/01/1991' THEN '' ELSE convert(varchar,h.sDestructionDate,103) END AS 'sDestructionDate' FROM tblDocumentHeader h ```

Original source