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
                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()
        )
                        If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With