SQL Server Isnull Returning 1900-01-01 when field is null

Question

The following piece of code is returning 1900-01-01 when the DOB field is null. I wanted (and expected) it to return an empty string ('') but it's not. How should I proceed to get my desired results?

isnull(convert(date,DOB,1),'')

Answer 1

You can't get an empty string because you're returning the DATE value type from ISNULL.

Per the docs, ISNULL

Returns the same type as check_expression. If a literal NULL is provided as check_expression, returns the datatype of the replacement_value. If a literal NULL is provided as check_expression and no replacement_value is provided, returns an int.

If you're checking whether or not the value is NULL, there is no need to convert it to a date, unless you wanted to return a date value (which you don't seem to).

Instead, use:

SELECT ISNULL( DOB , '')

Which will return

''

if the value is NULL.

A NULL date is NULL (no value). An empty string, on the other hand, evaluates to 0, which in SQL Server is implicitly an integer representing the number of days since 1900-01-01.

Answer 2

This will convert the NULL date to space. The application layer (Excel) handles space fine

ISNULL (CONVERT(varchar(50), w.TRANSACTIONDT, 120), '')  as wTRANSACTIONDT

Answer 3

Just create a view

CREATE VIEW test_view
AS
SELECT case when year(DOB)<=1900 then null else DOB end as DOB,sometext,id from test;

and then use it instead original table: select * from test_view