3

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.

SSMS Capture

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.

2

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!

  • @sp_BitzErik Thank you. I was hoping I could avoid having to write a bunch of CONVERT statements (DocDate is just one of many columns to be transformed), but it appears I'll have to bite the bullet. The behavior seems odd given that the documentation is somewhat misleading. In my mind, their statement: If a literal NULL is provided as check_expression, returns the datatype of the replacement_value. Since my replacement_value of '' is a string, I would assume I could return an empty string. Do you have any knowledge on why this behavior exists for date data types? – PicoDeGallo Dec 5 '17 at 21:25
  • 3
    FYI - The reason for this is implicit data conversion. IsNull takes its output data type from the first parameter, which in this case is a DateTime. When the value is null, you are substituting an empty string value, which is then implicitly converted to a date time. Since it is an empty string, it is converted internally to a zero, which equates to the date 1900-01-01. This is why the solution requires another Convert statement to work around the issue. – Shane Estelle Dec 5 '17 at 21:31
  • @PicoDeGallo updated answer. – Erik Darling Dec 5 '17 at 21:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.