How to use CASE statement in SQL WHERE clause

Question

Framework: ASP.NET webforms.

I have two textboxes, one to input start datetime, and the other for end datetime. I also have a dropdownlist to allow users to select between "Local Time" and "UTC".

Scenarios are

1. If user selects "Local Time" in dropdownlist, retrieve result between @StartDateTime and @EndDateTime in [LocalTimeColumn] column

2. If user selects "UTC" in dropdownlist, retrieve result between @StartDateTime and @EndDateTime in [UTCColumn] column

The SQL query I wrote doesn't work:

SELECT *
FROM [vmc]
WHERE 
    @DropDownList = CASE
                       WHEN @DropDownList = "Local Time"
                          THEN ([LocalTimeColumn] BETWEEN @StartDateTime AND @EndDateTime)
                          ELSE ([UTCColumn] BETWEEN @StartDateTime AND @EndDateTime)
                    END

Answer 1

The predicate of a CASE expression (i.e. what follows THEN and ELSE) has to be a scalar value, rather than another logical expression. You can make your WHERE clause do what you want with a slight refactor:

SELECT *
FROM [vmc]
WHERE
    (@DropDownList = "Local Time" AND
     [LocalTimeColumn] BETWEEN @StartDateTime AND @EndDateTime) OR
    [UTCColumn] BETWEEN @StartDate AND @EndDate;