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

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.

Answer 2

You're still selecting a DateTime column, and so the result of that expression still needs to be a DateTime value rather than a string. To suggest an appropriate work-around, we'll need to know more about what you're really trying to do with this data.

Answer 3

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 4

You can't as such directly. It's easier to trap NULL in the client and change it to "no" there.

However, you could use a token value such as "17530101" which is a valid datetime, or CONVERT SomeDateTime first to varchar.

Otherwise, we need more info on why etc