Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using BETWEEN in SQL CASE where clause

Tags:

sql

case

where

I Have a form, where the user pulls a report. In the form they can choose a start date and an end date, or pass through a null for both values. If they choose null, it returns all records where effectivedate < GETDATE() The CASE statement doesn't seem to like between, nor does it like '<' operators

Here is my script

SELECT * FROM tbReport
WHERE 
    EffectiveDate 
    CASE 
        WHEN (@StartDate IS NOT NULL AND @EndDate IS NOT NULL) 
        THEN BETWEEN (@StartDate AND @EndDate)          
         ELSE 
                  THEN < GETDATE()

    END
like image 745
MeltdownZA Avatar asked Mar 24 '11 08:03

MeltdownZA


1 Answers

You could rewrite it without a case, like:

SELECT  * 
FROM   tbReport
WHERE   (
            @StartDate is not null 
            and 
            @EndDate is not null
            and 
            EffectiveDate between @StartDate AND @EndDate
        )
        or
        (
            (
                @StartDate is null 
                or 
                @EndDate is null
            )
            and 
            EffectiveDate < getdate()
        )
like image 190
Andomar Avatar answered Oct 13 '22 00:10

Andomar