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?

Locomotion

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

Urbannal

Thanks - I'm not trying to convert CreatedDate to an empty string, I'm trying to test for its value and output either its value or an empty string. I've tried to do this at the presentation layer as well, but the value test isn't working. When I try your CASE statement, I still get 1900-01-01 00:00:00.000 when the date is 1/1/1900. – Locomotion

@Locomotion But you are trying to convert an empty string to a datetime when you did this: CASE WHEN something THEN '' ELSE SomeDateTime END – Aglet

@Locomotion output as a string = CONVERT to a string, even if you didn't explicitly say CONVERT. You can't just magically change a valid datetime to a string. – Urbannal

Aaron - Okay, now I understand. I had no idea this was what was going on under the hood. It's working now if I just test for the value and use CAST if I'm actually outputting the date. Thank you so much! – Locomotion

Answer 2

select  ISNULL(CONVERT(VARCHAR(23), WorkingDate,121),'') from uv_Employee

Southwest

Answer 3

Try this code

(case when CONVERT(VARCHAR(10), CreatedDate, 103) = '01/01/1900' then '' else CONVERT(VARCHAR(24), CreatedDate, 121) end) as Date_Resolved

Pedicure

Answer 4

If you CAST your data as a VARCHAR() instead of explicitly CONVERTing your data you can simply

SELECT REPLACE(CAST(CreatedDate AS VARCHAR(20)),'Jan  1 1900 12:00AM','')

The CAST will automatically return your Date then as Jun 18 2020 12:46PM fix length strings formats which you can additionally SUBSTRING()

SELECT SUBSTRING(REPLACE(CAST(CreatedDate  AS VARCHAR(20)),'Jan  1 1900 12:00AM',''),1,11)

Output

Jun 18 2020

Homestead

Answer 5

Use this inside of query, no need to create extra variables.

CASE WHEN CreatedDate = '19000101' THEN '' WHEN CreatedDate =
'18000101' THEN ''  ELSE CONVERT(CHAR(10), CreatedDate, 120) + ' ' +
CONVERT(CHAR(8), CreatedDate, 108) END as 'Created Date'

Works like a charm.

Strickman

Answer 6

Two nitpicks. (1) Best not to use string literals for column alias - that is deprecated. (2) Just use style 120 to get the same value.

    CASE 
      WHEN CreatedDate = '19000101' THEN '' 
      WHEN CreatedDate = '18000101' THEN '' 
      ELSE Convert(varchar(19), CreatedDate, 120)
    END AS [Created Date]

Tillage

While I agree with nitpick 1, where is anyone on this page using a string literal for a column alias? – Urbannal

Answer 7

An alternate solution that covers both min (1/1/1900) and max (6/6/2079) dates:

ISNULL(NULLIF(NULLIF(CONVERT(VARCHAR(10), CreatedDate, 120), '1900-01-01'), '2079-06-06'), '').

Whatever solution you use, you should do a conversion of your date (or datetime) field to a specific format to bulletproof against different default server configurations.

See CAST and CONVERT on MSDN: https://msdn.microsoft.com/en-us/library/ms187928.aspx

Allwein

Answer 8

Simpler method that worked. ISNULL Nested CAST function can remove 1900-1-1 value if data is NULL

ISNULL(CAST(CAST(<<DateColumn>> AS DATE) AS Varchar),' ') [<<Date Column Name>>]

Aaren