SQL Server 2016 ISNULL Returning 1900-01-01

Question

I have a datetime2 column called DocDate that I am trying to evaluate an ISNULL check against to return a blank '' value for instead of NULL.

A snippet of the statement:

SELECT TOP 100
    DocumentId
    ,DocDate
    ,ISNULL(DocDate,'') AS 'DocDateEmpty'
FROM
    DocDetails
WHERE
    DocDate IS NULL

This returns: 1900-01-01 00:00:00.0000000

My WHERE clause clearly brings back only the rows that are explicitly NULL, so it is not an issue of DocDate being an empty string and evaluating to 0.

How can DocDate IS NULL evaluate correctly but ISNULL fails?

Per MSDN, ISNULL should evaluate the literal NULL value correctly and return an empty value.

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.

Answer 1

This will happen with DATETIME as well as DATETIME2:

DECLARE @dummy DATETIME2 = NULL
SELECT @dummy, ISNULL(@dummy, N'')
GO 

DECLARE @dummy DATETIME = NULL
SELECT @dummy, ISNULL(@dummy, N'')
GO 

Along with DATE and TIME

DECLARE @dummy DATE = NULL
SELECT @dummy, ISNULL(@dummy, N'')
GO 

DECLARE @dummy TIME = NULL
SELECT @dummy, ISNULL(@dummy, N'')
GO 

I think the easiest way around it is to add a second CONVERT to a string:

DECLARE @dummy DATETIME2 = NULL
SELECT @dummy, ISNULL(CONVERT(NVARCHAR(30), @dummy), N'')
GO 

Which returns an empty string as desired.

To answer your question about why, well, let's look at another scenario:

DECLARE @dummy INT = NULL 
SELECT @dummy, ISNULL(@dummy, '')

The ISNULL here returns 0. If you're not up on how SQL Server stores and deals with dates, see my Q&A here for more information.

In short, if you run SELECT CONVERT(DATETIME, 0), you get a familiar result. There's no such thing as an empty number, and there's no conversion of an empty string to a numeric expression.

Hope this helps!