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

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),'')

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.

StackExchange Database Administrators Q#89942, answer score: 24