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

Question

I'm testing some SQL query for return value.

The table I'm selecting from has a value NULL in a CloseDate field. I have a logic that checks if it is NULL, return an empty string. However, it returns 01/01/1900

declare @value varchar(50) = '2346677888'
    Set @Value=@Value +'%'

    select CONVERT(varchar,ISNULL(m.CloseDate,''),101) as CloseDate from( 
    SELECT m.EdsClosedDate AS CloseDate
    FROM [dbo].[tblMyTable] m                   
    WHERE MID like @value) m

What's wrong with the query?

I believe this is related to your previous question https://stackoverflow.com/q/54835384/1260204. When querying data with c# code it is best to always leave your data in native format. Example: do not convert a sql date to a varchar and then send it to c#. Instead leave it as a sql date/time so that the c# code can better work with the values. Only the presentation layer (the part of the code that displays the value) should convert the value to a string. — Igor, Feb 22 '19 at 22:20
Thank you, Igor. The web service was already developed with a property of string value. So, to minimize changes for now I will do a conversion on a database level — gene, Feb 22 '19 at 22:48

score 1 · Accepted Answer · answered Feb 22 '19 at 22:11

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

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

1 Answers1