Blank Values in Date Column Returning as 1900/01/01 on Running Select Statement

Blank values in Date column returning as 1900/01/01 on running SELECT statement

You dont need to do the string manipulation as you have shown in your question. If you have dates stored in mm/dd/yyyy format just cast it as DATE.

SELECT cast(a.[PAYOFF DATE] AS DATE) 
FROM MTG a 

For 1900-01-01 values, since you are converting from a string data type to Date, String datatype can have Empty strings but Date datatype cannot have empty date values, It can have either a date value or NULL value.

Therefore you need to convert the empty string to nulls before you convert it to date. 1900-01-01 is just a default value sql server puts in for you because Date datatype cannot have an empty value.

You can avoid having this sql server default value by doing something like this.

SELECT cast(NULLIF(a.[PAYOFF DATE],'') AS DATE) 
FROM MTG a 

Why SQL query returns '01/01/1900' instead of an empty string when Date field is NULL?

This expression:

select CONVERT(varchar, ISNULL(m.CloseDate, ''), 101) as CloseDate

is more clearly written as:

select CONVERT(varchar(255), COALESCE(m.CloseDate, 0), 101) as CloseDate

That is, the empty string is equivalent to a date value of "0". SQL Server starts counting dates from 1900-01-01, which is why you see that in your results.

I'm not sure what you are expecting. If you want an empty string, assign that after converting the value to a string:

select COALESCE(CONVERT(varchar(255), m.CloseDate, 101), '') as CloseDate

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

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

Null Date return 1900-01-01

If I understood correctly what you need:

isnull(cast(convert(date, isnull([ADRCBook Details].RCBDate, [Transaction Details].TDJournalDate), 112) as varchar), '')

Of course instead of cast you can use convert. The main point is to discard the date type information.

Return empty string if date contains '1900'

If the column is a date, and you want a formatted value back, then convert both sides to the relevant type, e.g.

CASE 
    WHEN YEAR(StartDate) = 1900 THEN ''
    ELSE CONVERT(VARCHAR, StartDate, 105) -- Adjust datetime format to suit -- see also http://msdn.microsoft.com/en-us/library/ms187928.aspx
END AS SD

Return empty string if date contains '1900'

If the column is a date, and you want a formatted value back, then convert both sides to the relevant type, e.g.

CASE 
    WHEN YEAR(StartDate) = 1900 THEN ''
    ELSE CONVERT(VARCHAR, StartDate, 105) -- Adjust datetime format to suit -- see also http://msdn.microsoft.com/en-us/library/ms187928.aspx
END AS SD

Stored procedure to suppress default values of '1900-01-01 00:00:00.000'

The problem is that all possible values of a CASE statement must have the same DataType, and the most restrictive possibility will be used.

You are trying to return an empty string in one case, but in the other case you return a DateTime type. That means that the empty string will be implicitly converted to a DateTime, which will default to 1900-01-01.

If you CAST the datetime value to a string, then both sides will return a string and you will be able to return an empty string. Like so:

CASE
    WHEN cs.trandate = '1900-01-01 00:00:00.000' THEN ''
    ELSE CONVERT(varchar(31),cs.trandate)
END AS lasttrandate,

Of course this is only a useful answer if you can accept a having a string for an output datatype for that column. If you need the datatype to be a date-time, then you will have to accept the 1900-01-01 output and handle it in your front end.

---

Related Topics