Converting SQL Server null date/time fields

Question

Whenever the value is null for this query

SELECT ISNULL(someDateTime,'')
FROM  someTable

the result is

someDateTime  
------------
1900-01-01 00:00:00.000

I want it to be "No", so if I run this:

SELECT ISNULL(someDateTime,'No')
FROM  someTable

then there's this error:

Conversion failed when converting datetime from character string.

How to do it? Thanks in advance!

Answer 1

The result of the expression will need to be a single type. If you want a character string (and you do, since 'No' is not a DateTime), you'll need to convert the datetime to such a string:

SELECT ISNULL(cast(someDatetime as varchar(20)), 'No') FROM someTable

As others have suggested, though, code like this smells bad, and you may want to pass the null to a client component and do the conversion there.

Answer 2

isnull() is trying to convert the second argument to the datatype of the field you specify in the first argument.

If you are going to be returning a string you need to cast the DateTime field to a string type so that isnull() can work properly - see Michael Petrotta's answer for a way to accomplish this.