Why do the return results of IIF and ISNULL not match my expectation when using date values?

I am trying to use IIF() to return the cast of a column as a date but only when the value isn’t a blank string [the values are not being stored as a null in the database I am working with].

I tried to use ISNULL as a better intermediary but because a date returns as 1900-01-01 it isn’t working correctly. It seems to work with other data types (int).

Example table

CREATE TABLE #temp_test (
[reg_dt] varchar(10)
)
insert into #temp_test SELECT ''
insert into #temp_test SELECT ''
insert into #temp_test SELECT ''
insert into #temp_test SELECT ''
INSERT INTO #temp_test select '20080509'
select * from #temp_test

Example of IIF test (appears to function properly, I have also tested the inverse =”, ‘false’, ‘true’); when I uncomment the try_convert and remove ‘true’, an error is encountered

SELECT IIF([reg_dt] != '', 'true'
       --TRY_CONVERT(date, [reg_dt], 112)
       , 'false') [reg_dt]
FROM   [#temp_test];

Example of ISNULL (returns 1900-01-01)

SELECT ISNULL(TRY_CONVERT( DATE, [reg_dt], 112), '') [reg_dt] from [#temp_test]

I expect the output to be a blank value if the existing value was blank and, if not, a date value to be returned. I think converting to date before checking for null values would probably be best, because if an invalid date is passed (such as 04/31/2019) then that would be converted to a blank value which is the preferred functionality in this case.

The below code DOES work but I’m really not sure if this is the best way to do it.

select ISNULL(NULLIF(CONVERT(VARCHAR(10), CAST([reg_dt] as date), 112), '19000101'), '') as [reg_dt] from #temp_test

Answer

I expect the output to be a blank value if the existing value was blank and, if not, a date value to be returned.

Every column from the result set must have a data type. DATE and DATETIME data types can’t hold empty stings, only valid dates or NULL. If you want to see an empty string, then you will have to work with a string data type, like VARCHAR.

Check these examples:

DECLARE @Varchar VARCHAR(100) = ''

SELECT
    EmptyVarchar = @Varchar,
    EmptyVarcharAsDate = CONVERT(DATE, @Varchar),
    CaseExpression = CASE WHEN @Varchar = '' THEN '' ELSE CONVERT(DATE, @Varchar) END

Result:

EmptyVarchar    EmptyVarcharAsDate  CaseExpression
                1900-01-01          1900-01-01

If we check which data type the CASE is actually returning…

DECLARE @Varchar VARCHAR(100) = ''

SELECT
    ResultType = SQL_VARIANT_PROPERTY(
        CASE WHEN @Varchar = '' THEN '' ELSE CONVERT(DATE, @Varchar) END, 
        'BaseType')

Result:

ResultType
date

You see that the CASE (an IIF works similar) is returning a date type, so the empty string is implicitly converting to the default date of 1900-01-01.

A way to handle dates and return them as an empty strings when they can’t be converted would be the following:

DECLARE @VarcharDates TABLE (VarcharDate VARCHAR(100))

INSERT INTO @VarcharDates (VarcharDate)
VALUES
    (''),
    (NULL),
    ('NULL'),
    ('15'),
    ('2019'),
    ('2019-05'),
    ('31/01/2000'),
    ('1800-01-01'),
    ('20190503'),
    ('20190503111111')

SELECT
    V.VarcharDate,
    DateAsVarchar = CASE 
        WHEN V.VarcharDate = '' THEN ''
        ELSE ISNULL(
            CONVERT(VARCHAR(100), TRY_CONVERT(DATE, V.VarcharDate, 112)),
            '') END
FROM
    @VarcharDates AS V

Result:

VarcharDate     DateAsVarchar

NULL    
NULL    
15  
2019            2019-01-01
2019-05 
31/01/2000  
1800-01-01  
20190503        2019-05-03
20190503111111