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!
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.
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.
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.
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
In your Update Stored Proc: Update your previous date value to a new Null value:
.Parameters.AddWithValue("@Date_Value", Nothing).IsNullable = True
