SQL add conditional in WHERE clause

Question

I have a table with two columns that are "date" type: date1 and date2.

date1 CAN´T be null but date2 CAN be null

I have the following query:

SELECT
*
FROM table1
WHERE date1 BETWEEN "2021-01-01" AND "2021-12-31"

How can I add a conditional part in where clause, that if date2 IS NOT NULL, apply the following restriction/condition AND date2 BETWEEN "2021-01-01" AND "2021-12-31"

If the condition only applies when `date2` is not null, then `date2 IS NULL` is also acceptable. You can use the `OR` keyword and some parenthesis to include it in your `WHERE` clause: `AND (date2 IS NULL OR date2 BETWEEN "2021-01-01" AND "2021-12-31)"`. — D M, Jun 16 '21 at 14:14
@DM If that's an answer, post it as an answer, not as a comment. If you answer people in the comments, the question remains in the "unanswered questions" category forever. — Bill Karwin, Jun 16 '21 at 14:15
@BillKarwin I appreciate it, but I'm nearly positive this is a duplicate. I'm searching for a good target now. — D M, Jun 16 '21 at 14:16
[Related answer 1](https://stackoverflow.com/a/3649184/14956277) and [related answer 2](https://stackoverflow.com/a/59047547/14956277) discussing SARGability of various optional `WHERE` clause condition approaches. — D M, Jun 16 '21 at 14:26
I´m not using procedures and variables, but if I change the variables with conditions as you say in [post](https://stackoverflow.com/questions/10185638/optional-arguments-in-where-clause), the first answer in the proposed post is what im loking for — SensacionRC, Jun 16 '21 at 14:29

score 0 · Answer 1 · answered Jun 16 '21 at 14:17

You can use this -

if date2 is null then apply date2 condition else apply date1 condition. You can play around if you want something else.

SELECT
*
FROM table1
WHERE 
case when  date2 is null AND 
date1 BETWEEN "2021-01-01" AND "2021-12-31" then 1
when date2 is not null AND 
date2 BETWEEN "2021-01-01" AND "2021-12-31" then 1
else 0
end =1

SQL add conditional in WHERE clause

1 Answers1