Discuss "Return Default Date If Null" in the SQL Development forum.

I have a query like the following:

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.

__________________
jmurrayhead
If you agree with me... click the icon!
If my post solved your problem, click the button in the lower right-hand corner of the post.

Join our Folding team: DeveloperBarn Folding

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

__________________
Quote of the Month:
Regret: It hurts to admit when you make mistakes - but when they're big enough, the pain only lasts a second.

Questions to Ponder:
Could it be that all those trick-or-treaters wearing sheets aren’t going as ghosts but as mattresses?

iif([sarcasm]=true,iif([you have to ask]=true,"didn't work","ha ha ha"),"not sarcasm")
copyright © 2008 sbenj69

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.

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.

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
?

__________________
Wolffy
------------------------
Opinions expressed are my own and do not necessity reflect those of any sane person. Any code provided is intended to be an example and is provided AS IS. Rework for your specific environment may be required. Void where prohibited by law. Not valid in California. Your mileage may vary.