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

jmurrayhead · #1 (**permalink**) June 6th, 2008, 11:26 AM

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.

mehere · #2 (**permalink**) June 6th, 2008, 11:29 AM

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?

jmurrayhead · #3 (**permalink**) June 6th, 2008, 11:34 AM

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

mehere · #4 (**permalink**) June 6th, 2008, 11:36 AM

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

jmurrayhead · #5 (**permalink**) June 6th, 2008, 11:39 AM

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.

mehere · #6 (**permalink**) June 6th, 2008, 11:42 AM

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

jmurrayhead · #7 (**permalink**) June 6th, 2008, 11:44 AM

Quote:

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.

mehere · #8 (**permalink**) June 6th, 2008, 11:46 AM

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

jmurrayhead · #9 (**permalink**) June 6th, 2008, 11:48 AM

Quote:

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.

Wolffy · #10 (**permalink**) June 6th, 2008, 01:07 PM

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
?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
No Default Member Found for type	richyrich	.Net Development	5	May 29th, 2008 01:33 PM
Webform Default Button	Devwhiz	.Net Development	9	May 15th, 2008 01:15 PM

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

SQL Development - Structured Query Language (SQL) is the talk of databases. Discuss topics such as joins, triggers and other advanced SQL topics.