Search code examples
sqlconditional-statementstemp-tables

sql query condition on temporary column

I pondered on this one a little bit. Found a few question on SO but none of them addressed my problem.

What I am trying to do is compare two dates in a table, I am returning the greater of the two using a case statement

select <-- similar code but not actual code
...
case
when date1 > date2 then date1 else date2 end as lastDate

Everything fine upto this point. I get the greater date in my result. The problem is I want to apply a WHERE clause on LastDate. Since the lastDate is a temporary column, it won't be accept in WHERE clause I came up with this syntax

if @cur_date != null  <---- this is parameter to my store procedure, if not null then
case when date1 > date then date1 like '2011-%' else date2 like '2011-%'

But I get an error for the misplaced 'like' keyword. I think a statement can not be returned after the 'then' keyword in case statement. How do I do this? Do I have to use temporary table for this? I want to find the best approach.

Solution

  • Use your same syntax in the WHERE clause:

    WHERE case when date1 > date2 then date1 
      else date2 END
      like '2011-%'

    EDIT:

    Sample code for Date comparison:

    WHERE case when date1 > date2 then CAST(date1 as varchar)
      else CAST(date2 as varchar) END
      like '2011-%'