How do I use if condition in where condition?-CodePudding

How do I use if condition in where condition

I want to use settle_date when the trans_date is null;

select * from test
where date(trans_date) = '2022-05-01'

How do I have date(settle_date) = '' when trans_date is null?

CodePudding user response：

Use COALESCE:

SELECT *
FROM test
WHERE DATE(COALESCE(trans_date, settle_date)) = '2022-05-01';

Note that if the trans_date and settle_date columns are already dates, you don't need to cast to date:

SELECT *
FROM test
WHERE COALESCE(trans_date, settle_date) = '2022-05-01';

CodePudding user response：

You can use IFNULL:

SELECT * FROM test WHERE date(ISNULL(trans_date,settle_date))='2022-05-01'

CodePudding user response：

You can use COALESCE to replace NULL values by another value. So in your case you can do:

SELECT *
FROM test
WHERE DATE(COALESCE(trans_date, settle_date)) = '2022-05-01';

If these columns are datetime, i.e. have a time component, then don't cast to date but instead use:

SELECT *
FROM test
WHERE COALESCE(trans_date, settle_date) >= '2022-05-01' AND
      COALESCE(trans_date, settle_date) <  '2022-05-02';

CodePudding user response：

Although "COALESCE" is the best answer, you also do it like this:

SELECT oneField,
    CASE
        WHEN trans_date IS NULL THEN settle_date 
        ELSE trans_date 
    END AS myDate,
    otherField
FROM someTable