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