How can I rewrite this statement?
I have declared a variable @date
SELECT @date = CASE
WHEN MAX(dt) IS NULL THEN '31/12/2009'
ELSE MAX(dt) + 1
END
FROM mytab
A code snippet where I am using the local variable
CASE
WHEN MAX(DateValue)= @date THEN NULL
ELSE CONVERT(varchar(10), CONVERT(datetime, MAX(DateValue)), 103)
END AS newdt
I don't want to use the @date local variable in the case statement but want to get the same effect. I tried:
CASE
WHEN MAX(DateValue)= @isnull(dt,开发者_如何学JAVA '31/12/2009')
ELSE CONVERT(varchar(10), CONVERT(datetime, MAX(DateValue)), 103)
END AS newdt
but receive an error. Any idea of getting rid of this?
OK, firstly you need to use
ISNULL(dt, '31/12/2009')
Without the @.
Secondly, you will need to give a more complete query for us to check. Also, specify the error you are receiving.
You could also include the original select in the second select. Something like this:
CASE
WHEN MAX(DateValue)= (SELECT CASE WHEN MAX(dt) IS NULL THEN '31/12/2009' ELSE MAX(dt) + 1 END FROM mytab)
ELSE CONVERT(varchar(10), CONVERT(datetime, MAX(DateValue)), 103)
END AS newdt
CASE
WHEN MAX(DateValue)= isnull(dt, '31/12/2009')
ELSE CONVERT(varchar(10), CONVERT(datetime, MAX(DateValue)), 103)
END AS newdt
精彩评论