is null

[cards4]

Hello,
I am using a case statement to determine if a date is null or not. Here is my code:
SELECT *,
length_of_service = case rehire_date
when rehire_date is null THEN
round(convert(decimal, datediff(dd, hire_date, getDate()))/365.2422,2)
ELSE
round(convert(decimal, datediff(dd, rehire_date, getDate()))/365.2422,2)
END,
However, I'm getting "incorrect syntax near the keyword 'is'" error. I just want to see if the rehire date is null or not. I'm not entirely sure what I'm doing wrong. Please help! Thank you.

 

[gmmastros]

Try it this way.

SELECT *, 
length_of_service = case [!]When rehire_date Is NULL [/!]
Then round(convert(decimal, datediff(dd, hire_date, getDate()))/365.2422,2)
ELSE round(convert(decimal, datediff(dd, rehire_date, getDate()))/365.2422,2)
END

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AlexCuse]

I think you want your case statement to read like this:

case rehire_date
[b]when null[/b] THEN
round(convert(decimal, datediff(dd, hire_date, getDate()))/365.2422,2)
ELSE
round(convert(decimal, datediff(dd, rehire_date, getDate()))/365.2422,2)
END

You are kind of mixing the two acceptable syntaxes for case statements there.

Hope it helps,

Aelx

A wise man once said
"The only thing normal about database guys is their tables".

 

[AlexCuse]

George I am stalking you this morning

A wise man once said
"The only thing normal about database guys is their tables".

 

[cards4]

It worked! Thank you very much.

 

[gmmastros]

Alex,

Your syntax doesn't work because you cannot compare the value to null that way. Check it out.

Declare @Temp Table(Data int)

Insert Into @Temp Values(1)
Insert Into @Temp Values(NULL)

Select Case Data When Null Then 'unknown' Else 'known' end From @Temp

Select case when Data Is NULL Then 'unknown' Else 'known' end From @Temp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AlexCuse]

Right you are George. Thanks for setting me straight.

Have a

A wise man once said
"The only thing normal about database guys is their tables".