DeveloperBarn Forums

DeveloperBarn

Programming & IT forum

Return Default Date If Null

This is a discussion on Return Default Date If Null within the SQL Development forums, part of the Databases category; I have a query like the following: Code: "SELECT " & _ [last_name]," & _ [first_name]," & _ [hire_date]," & ...

Go Back   DeveloperBarn Forums > Databases > SQL Development

Return Default Date If Null Return Default Date If Null

Register FAQ Social Groups Calendar Search Today's Posts Mark Forums Read

Reply
Page 1 of 2 1 2

 

LinkBack Thread Tools Display Modes
  #1  
Old June 6th, 2008, 11:26 AM
jmurrayhead's Avatar
The Barnfather
  
Join Date: Mar 2008
Real name: Jason
Location: Washington, D.C.
Posts: 1,964
Blog Entries: 8
Rep Power: 15
jmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud of
Default 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.
__________________
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.

If you like it here...throw us a few bones to help support us.

Join our Folding team: DeveloperBarn Folding
Reply With Quote
  #2  
Old June 6th, 2008, 11:29 AM
mehere's Avatar
Super Sarcasm Mistress
  
Join Date: Mar 2008
Real name: Joanne
Location: Wide Awake In Dreamland
Posts: 375
Rep Power: 6
mehere is just really nicemehere is just really nicemehere is just really nicemehere is just really nicemehere is just really nice
Default
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?
__________________
Quote of the Month:
Mistakes: It could be that the purpose of your life is only to serve as a warning to others.

Questions to Ponder:
Why do banks charge you a "non-sufficient funds fee" on money they already know you don't have?

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

Sarchasm: The gulf between the author of sarcastic wit and the person who doesn't get it.
Reply With Quote
  #3  
Old June 6th, 2008, 11:34 AM
jmurrayhead's Avatar
The Barnfather
  
Join Date: Mar 2008
Real name: Jason
Location: Washington, D.C.
Posts: 1,964
Blog Entries: 8
Rep Power: 15
jmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud of
Default
This is a VB.Net application using SQL Server 2000.
Reply With Quote
  #4  
Old June 6th, 2008, 11:36 AM
mehere's Avatar
Super Sarcasm Mistress
  
Join Date: Mar 2008
Real name: Joanne
Location: Wide Awake In Dreamland
Posts: 375
Rep Power: 6
mehere is just really nicemehere is just really nicemehere is just really nicemehere is just really nicemehere is just really nice
Default
then you would need to do something in the .NET app to account for NULL values?
Reply With Quote
  #5  
Old June 6th, 2008, 11:39 AM
jmurrayhead's Avatar
The Barnfather
  
Join Date: Mar 2008
Real name: Jason
Location: Washington, D.C.
Posts: 1,964
Blog Entries: 8
Rep Power: 15
jmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud of
Default
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.
Reply With Quote
  #6  
Old June 6th, 2008, 11:42 AM
mehere's Avatar
Super Sarcasm Mistress
  
Join Date: Mar 2008
Real name: Joanne
Location: Wide Awake In Dreamland
Posts: 375
Rep Power: 6
mehere is just really nicemehere is just really nicemehere is just really nicemehere is just really nicemehere is just really nice
Default
then without changing the query ... i don't see how you can. unless you update the database and put a default value in there.
Reply With Quote
  #7  
Old June 6th, 2008, 11:44 AM
jmurrayhead's Avatar
The Barnfather
  
Join Date: Mar 2008
Real name: Jason
Location: Washington, D.C.
Posts: 1,964
Blog Entries: 8
Rep Power: 15
jmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud of
Default
Quote:
Originally Posted by mehere View Post
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.
Reply With Quote
  #8  
Old June 6th, 2008, 11:46 AM
mehere's Avatar
Super Sarcasm Mistress
  
Join Date: Mar 2008
Real name: Joanne
Location: Wide Awake In Dreamland
Posts: 375
Rep Power: 6
mehere is just really nicemehere is just really nicemehere is just really nicemehere is just really nicemehere is just really nice
Default
ugh ... you said it was hardcoded ... wow ... need to get my head out of the clouds. use what i posted then.
Reply With Quote
  #9  
Old June 6th, 2008, 11:48 AM
jmurrayhead's Avatar
The Barnfather
  
Join Date: Mar 2008
Real name: Jason
Location: Washington, D.C.
Posts: 1,964
Blog Entries: 8
Rep Power: 15
jmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud ofjmurrayhead has much to be proud of
Default
Quote:
Originally Posted by mehere View Post
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.
Reply With Quote
  #10  
Old June 6th, 2008, 01:07 PM
Wolffy's Avatar
Wolfmaster
  
Join Date: Mar 2008
Real name: Wolff
Location: Peoria, IL
Posts: 779
Blog Entries: 1
Rep Power: 9
Wolffy is a splendid one to beholdWolffy is a splendid one to beholdWolffy is a splendid one to beholdWolffy is a splendid one to beholdWolffy is a splendid one to beholdWolffy is a splendid one to beholdWolffy is a splendid one to behold
Default
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.
Reply With Quote
Reply
Page 1 of 2 1 2

  DeveloperBarn Forums > Databases > SQL Development

Bookmarks

« Previous Thread | Next Thread »
Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

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


All times are GMT -4. The time now is 10:58 AM.

Contact Us - DeveloperBarn.com - Top

Copyright ©2008-2010, DeveloperBarn

Content Relevant URLs by vBSEO 3.3.2