Show empty string when date field is 1/1/1900

Question

I'm querying a database like so:

SELECT DISTINCT 
CASE WHEN CreatedDate = '1900-01-01 00:00:00.000' THEN '' ELSE CreatedDate END AS CreatedDate
FROM LitHoldDetails

lhd.CreatedDate is a DateTime field and is non-nullable. I want to display an empty string if the field is the minimum date (1/1/1900), but my CASE statement doesn't work; CreatedDate displays 1900-01-01 00:00:00.000 in my query when that value is in the database. I'm using SQL Server 2008 R2. What am I doing wrong?

Answer 1

When you use a CASE expression (not statement) you have to be aware of data type precedence. In this case you can't just set a DATETIME to an empty string. Try it:

SELECT CONVERT(DATETIME, '');

One workaround is to present your date as a string:

CASE WHEN CONVERT(DATE, CreatedDate) = '1900-01-01' -- to account for accidental time
  THEN ''
  ELSE CONVERT(CHAR(10), CreatedDate, 120)
    + ' ' + CONVERT(CHAR(8), CreatedDate, 108)
END 

Or you could fiddle with the presentation stuff where it belongs, at the presentation tier.

Here is an example that works exactly as you seem to want:

DECLARE @d TABLE(CreatedDate DATETIME);

INSERT @d SELECT '19000101' UNION ALL SELECT '20130321';

SELECT d = CASE WHEN CreatedDate = '19000101'
  THEN ''
  ELSE CONVERT(CHAR(10), CreatedDate, 120)
    + ' ' + CONVERT(CHAR(8), CreatedDate, 108)
END FROM @d;

Results:

d
-------------------
                    <-- empty string
2013-03-21 00:00:00