CASE / NULL Problem

Hello!
I want to set a value depending on a condition but I can't seem to get it
right when the original value is NULL.
Select bla, bla... FROM bla...
*****************************
DellyDate=CASE DelivDate
WHEN NULL THEN
'1999-12-31'
ELSE
LEFT(DelivDate,4) + '-' + Substring(DelivDate,5,2) + '-' +
RIGHT(DelivDate,2)
END
***********************************'
This code parses successfully, but even when DelivDate actually is NULL,
DellyDate never has the value '1999-12-31'. What am I doing wrong here?You'll need to use IS NULL.
DellyDate=CASE WHEN DelivDate IS NULL
THEN
'1999-12-31'
ELSE
LEFT(DelivDate,4) + '-' + Substring(DelivDate,5,2) + '-' +
RIGHT(DelivDate,2)
END
or alternatively
DellyDate=COALESCE(LEFT(DelivDate,4) + '-' + Substring(DelivDate,5,2) +
'-' +
RIGHT(DelivDate,2) ,'1999-12-31' )|||DellyDate = CASE WHEN DelivDate IS NULL THEN '1999-12-13' ELSE .... END
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Clarkie" <clarkbones@.rock.sendmenot.etmail.com> wrote in message
news:eKVi$lQJGHA.1728@.TK2MSFTNGP09.phx.gbl...
> Hello!
> I want to set a value depending on a condition but I can't seem to get it
> right when the original value is NULL.
> Select bla, bla... FROM bla...
> *****************************
> DellyDate=CASE DelivDate
> WHEN NULL THEN
> '1999-12-31'
> ELSE
> LEFT(DelivDate,4) + '-' + Substring(DelivDate,5,2) + '-' +
> RIGHT(DelivDate,2)
> END
> ***********************************'
> This code parses successfully, but even when DelivDate actually is NULL,
> DellyDate never has the value '1999-12-31'. What am I doing wrong here?
>|||you could also
coalesce(convert(varchar(10), convert(datetme, DelivDate), 120),
'1999-12-31')
"Clarkie" wrote:

> Hello!
> I want to set a value depending on a condition but I can't seem to get it
> right when the original value is NULL.
> Select bla, bla... FROM bla...
> *****************************
> DellyDate=CASE DelivDate
> WHEN NULL THEN
> '1999-12-31'
> ELSE
> LEFT(DelivDate,4) + '-' + Substring(DelivDate,5,2) + '-' +
> RIGHT(DelivDate,2)
> END
> ***********************************'
> This code parses successfully, but even when DelivDate actually is NULL,
> DellyDate never has the value '1999-12-31'. What am I doing wrong here?
>
>|||Read the definition of the CASE expression. You are using a shorthand
for
SELECT ..
FROM ..
WHERE delly_date
=CASE WHEN delly_date = NULL -- always UNKNOWN !!!!
THEN '1999-12-31'
ELSE .. END ;|||Thanks for all the answers!
Problem solved!
//Clarkie
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138581718.965490.84740@.g14g2000cwa.googlegroups.com...
> Read the definition of the CASE expression. You are using a shorthand
> for
> SELECT ..
> FROM ..
> WHERE delly_date
> =CASE WHEN delly_date = NULL -- always UNKNOWN !!!!
> THEN '1999-12-31'
> ELSE .. END ;
>