RE: Case Statement in Where Clause 1

[allyne]

Hello Everyone,

I need to use a case statement in the where clause and I can't seem to get this to work. I don't think I'm using this correctly. My statement looks something like this:

Declare @FYDFrom as DateTime,
@FYDTo as DateTime,
@FYDFrom1 as DateTime,
@FYDTo1 as DateTime


Set @FYDFrom = '20090701'
Set @FYDTo = '20100701'

Set @FYDFrom1 = '20100701'
Set @FYDTo1 = '20110701'

SELECT *
FROM charges c
Where
Case When GetDate() < '20100701'
Then c.create_timestamp >= @FYDFrom and c.create_timestamp < @FYDTo
When GetDate() >= '20100701'Then c.create_timestamp >= @FYDFrom1 and c.create_timestamp < @FYDTo1
End

The error message I receive is Msg 102, Level 15, State 1, Line 16
Incorrect syntax near '>'

Any help would be appreciated!

Thanks!

 

[gmmastros]

It appears as though you are trying to use the Case/When syntax to control the logic of your query. Unfortunately, you cannot do it this way. There are other ways to accomplish the same results.

Declare @FYDFrom as DateTime,
    @FYDTo as DateTime,
    @FYDFrom1 as DateTime,
    @FYDTo1 as DateTime


Set    @FYDFrom = '20090701'
Set    @FYDTo = '20100701'

Set @FYDFrom1 = '20100701'
Set @FYDTo1 = '20110701'

SELECT  *
FROM  charges c
Where ( 
      GetDate() < '20100701' 
      and c.create_timestamp >= @FYDFrom 
      and c.create_timestamp < @FYDTo
      )
      Or 
      (
      GetDate() >= '20100701'
      And c.create_timestamp >= @FYDFrom1 
      and c.create_timestamp < @FYDTo1
      )

Alternatively (with the case/when syntax).

Declare @FYDFrom as DateTime,
    @FYDTo as DateTime,
    @FYDFrom1 as DateTime,
    @FYDTo1 as DateTime


Set    @FYDFrom = '20090701'
Set    @FYDTo = '20100701'

Set @FYDFrom1 = '20100701'
Set @FYDTo1 = '20110701'

SELECT  *
FROM  charges c
Where c.create_timestamp >= Case When GetDate() < '20100701' 
                                 Then @FYDFrom 
                                 Else @FYDFrom1
                                 End
      And c.create_timestamp < Case When GetDate() < '20100701' 
                                    Then @FYDTo
                                    Else @FYDTo1
                                    End

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[allyne]

WOW!!! Thanks! Both suggestions work great! would have never thought to use it that way!

Thanks again for your help!