With the help of google I have gotten the where statement to look like below but the program I am using says it is wrong without telling me what is wrong, and before anyone asks why I am doing what I am doing, the client wants the option to set both ends of the date range to the same date and have it populate with that dates info and just using WHERE BETWEEN was not working with that.
WHERE Date = Case WHEN @Y1=@Y2 THEN = @Y1 ELSE BETWEEN @Y1 AND @Y2 END
Any help is appreciated.
I have also tried adding additional where clauses such as (where between y1 and y2 OR date=Y1 or date=Y2) but that didn't seem to work ether.
The answer is, as it seems: you don't have to. A BETWEEN predicate on two equal values returns the same TRUE/FALSE answer as an equi predicate on the same value once:
See this test:
WITH
indata(dt) AS (
SELECT DATE '2023-04-01'
UNION ALL SELECT DATE '2023-04-02'
UNION ALL SELECT DATE '2023-04-03'
UNION ALL SELECT DATE '2023-04-04'
UNION ALL SELECT DATE '2023-04-05'
UNION ALL SELECT DATE '2023-04-06'
UNION ALL SELECT DATE '2023-04-07'
UNION ALL SELECT DATE '2023-04-08'
UNION ALL SELECT DATE '2023-04-09'
UNION ALL SELECT DATE '2023-04-10'
)
,
srch(fromdt,todt,descr) AS (
SELECT DATE '2023-04-01',DATE '2023-04-04','range'
UNION ALL SELECT DATE '2023-04-05',DATE '2023-04-05','equal'
)
SELECT
dt, descr
FROM indata CROSS JOIN srch
WHERE dt BETWEEN fromdt AND todt
ORDER BY descr,dt;
-- out dt | descr
-- out ------------+-------
-- out 2023-04-05 | equal
-- out 2023-04-01 | range
-- out 2023-04-02 | range
-- out 2023-04-03 | range
-- out 2023-04-04 | range
Do I need to comment the test or is it self explanatory?