Thread: Return Default Date If Null

I have a query like the following:

Code:

"SELECT " & _
    [last_name]," & _
    [first_name]," & _
    [hire_date]," & _
"FROM vw_people " & _
"WHERE id = @id"

Now, the people that designed the database have NULL values EVERYWHERE! It's a complete mess that I have to deal with. For some ungodly reason, someone's hire date is NULL. I know, makes no sense. Why would this person have a record if they don't work for us and don't have a hire date?

Anyway, is there a way via my above query to return a default date if "hire_date" is NULL? I'm using SQL Server 2000. This is a hard coded query unfortunately and I'm not sure if I can do some sort of SELECT CASE here.

try something like this

Code:

"SELECT " & _
    [last_name]," & _
    [first_name]," & _
    CASE WHEN [hire_date] IS NULL THEN 'default_date' ELSE [hire_date] END AS hire_date," & _
"FROM vw_people " & _
"WHERE id = @id"

sorry ... my bad ... didn't read that it's a hard coded query. what is it being used in?

This is a VB.Net application using SQL Server 2000.

then you would need to do something in the .NET app to account for NULL values?

That's the problem. For some reason certain .Net methods won't accept NULL dates. Which is why I'm trying to return a default value before it gets to the method.

then without changing the query ... i don't see how you can. unless you update the database and put a default value in there.

Originally Posted by mehere

then without changing the query ... i don't see how you can. unless you update the database and put a default value in there.

What do you mean 'without changing the query'? I want to change the query if I can get it to return the default date

And believe me, I'd much rather rid myself of NULL in this database, but that is not up to me, unfortunately.

ugh ... you said it was hardcoded ... wow ... need to get my head out of the clouds. use what i posted then.

Originally Posted by mehere

ugh ... you said it was hardcoded ... wow ... need to get my head out of the clouds. use what i posted then.

lol it is hard coded. I was asking if there was a way without TSQL to do this.

When you said 'change your query', I was confused as that was a vague statement.

So I'm assuming there is no way to do what I want and I'm going to have to put my right boot on when I go talk to the database morons.

Hmm, y'all seem to be posting in circles here JMH & mehere . Obviously sumpin' gotta change sumplace, so which would you like to do:
(a) Change the data
(b) Change the query
(c) Handle the null in code
(d) Write a sproc
(e) Kill the DB designer
?