MySQL query to return 1 and 0 based on difference between dates

Question

In MySQL Database I have a table ABC that consists of a column 'LastDate'.

LastDate which has datatype as DATETIME. The default value for this 'NULL'

I need to write a query for the table which would Return '1' in these cases. 1) If DATEDIFF(CURRENT_TIME,LastDate) is >15 or if DATEDIFF(CURRENT_TIME,LastDate) is NULL(i.e defaultVal).

return '0' if DATEDIFF(CURRENT_TIME,LastDate) is <15.

I tried to write an SQL query for this but was unable to do it. Please help me write this Query. Thanks in advance.

Update the question to show your SQL attempt. Hint, ```SELECT CASE END ...``` might be an option. — Jon Armstrong, Jul 26 '21 at 17:18
is there a particular row in ABC that you are testing? or you want your 0/1 results for each row? — ysth, Jul 26 '21 at 20:34

score 0 · Answer 1 · answered Jul 26 '21 at 17:49

You can be explicit about your logic:

select t.*,
       (case when DATEDIFF(CURRENT_TIME, LastDate) > 15 or
                  LastDate is null
             then 1 else 0
        end) as flag
from t;

This can be simplified to:

select t.*,
       coalesce(DATEDIFF(CURRENT_TIME, LastDate) <= 15, 1) as flag
from t;

MySQL query to return 1 and 0 based on difference between dates

1 Answers1