Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ITTrucker
Yak Posting Veteran
64 Posts |
 Posted - 2013-03-28 : 14:57:04
|
| 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
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-03-28 : 15:05:56
|
| 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 OptimizerTG |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-03-28 : 15:06:45
|
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) |
|
|
|
ITTrucker
Yak Posting Veteran
64 Posts |
Posted - 2013-03-28 : 15:35:30
|
| 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. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2013-03-29 : 13:30:44
|
| 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 datatypehttp://beyondrelational.com/modules/2/blogs/70/posts/10841/empty-string-and-default-values.aspxMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
ISNULL but checking for value instead