How can I rewrite this statement?

Question

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, '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?

Answer 1

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

Answer 2

CASE 
  WHEN MAX(DateValue)= isnull(dt, '31/12/2009') 
  ELSE CONVERT(varchar(10), CONVERT(datetime, MAX(DateValue)), 103) 
END AS newdt