16

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),'')
2
  • 6
    You're mixing data types up too much. Essentially what you're doing with NULL dates is saying SELECT CAST('' AS DATE). What data type is DOB? Jan 22, 2015 at 17:06
  • 1
    it's a varchar data type that I am converting to date
    – Juan Velez
    Jan 22, 2015 at 17:18

3 Answers 3

25

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.

0
-4

This will convert the NULL date to space. The application layer (Excel) handles space fine

ISNULL (CONVERT(varchar(50), w.TRANSACTIONDT, 120), '')  as wTRANSACTIONDT
-4

Just create a view

CREATE VIEW test_view
AS
SELECT case when year(DOB)<=1900 then null else DOB end as DOB,sometext,id from test;

and then use it instead original table: select * from test_view

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