SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 ISNULL but checking for value instead
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ITTrucker
Yak Posting Veteran

USA
55 Posts

Posted - 03/28/2013 :  14:57:04  Show Profile  Reply with Quote
Is there a function like ISNULL that checks for a specific value instead of NULLs? I have a date field that the program puts 19000101 in for the date instead of a null, so I want to use an alternate field if that date is 19000101. Is there something out there other than a big case statement?

It's for aging invoices so I already have a CASE WHEN to create the aging buckets and would need to change all 5 of my WHEN clauses to have subcases to check for that date.

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 03/28/2013 :  15:05:56  Show Profile  Reply with Quote
There's NULLIF which replaces a specific value with NULL.
so you could use something like:

ISNULL(NULLIF(col1, 19000101), col2)

Be One with the Optimizer
TG
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 03/28/2013 :  15:06:45  Show Profile  Reply with Quote
You can use a case esxpresion. There is also the NULLIF function:
DECLARE @DateColumn DATE;
DECLARE @OtherDateColumn DATE = SYSDATETIME();

SELECT COALESCE(NULLIF(@DateColumn, CAST('19000101' AS DATE)), @OtherDateColumn)
Go to Top of Page

ITTrucker
Yak Posting Veteran

USA
55 Posts

Posted - 03/28/2013 :  15:35:30  Show Profile  Reply with Quote
Exactly what I was looking for, Thanks for the help.

(Took a few minutes to get the syntax correct I already had a lot going on inside the case statement but cleaned it up and working perfectly)

Thanks again.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 03/29/2013 :  13:30:44  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Note that when you pass empty value to a DATETIME column, that would be converted to a default value of 1900-01-01 00:00:000 So if the date values are passed from a front end application, fix this there and pass NULL instead of empty string. You may be interested to know how empty string affect the default value across various datatype
http://beyondrelational.com/modules/2/blogs/70/posts/10841/empty-string-and-default-values.aspx

Madhivanan

Failing to plan is Planning to fail

Edited by - madhivanan on 03/29/2013 13:31:30
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000