Skip to content
Advertisement

MySQL CURDATE() + INTERVAL 1 DAY not returning as should

I am stumped!

I have a table that has a field job_date_start — And the DataType is set to DATETIME

If I SELECT job_date_start FROM table; from my table the output is:

+---------------------------+
|      job_date_start       |
+---------------------------+
|    2021-02-26 12:00:00    |
+---------------------------+

IF I SELECT NOW(); from the same table I get:

+---------------------------+
|           NOW()           |
+---------------------------+
|    2021-02-25 17:41:01    |
+---------------------------+

Why then, when I use the following, am I returning and empty result?

SELECT * FROM table WHERE job_date_start = curdate() + interval 1 day;

Per MANY Stack Overflow questions LIKE THIS ONE my syntax is correct .. I am not understanding something.

Advertisement

Answer

job_date_start seems to have a time component. You need a date range or just the date:

WHERE job_date_start >= curdate() + interval 1 day and
      job_date_start < curdate() + interval 2 day ;

Or:

WHERE DATE(job_date_start) = curdate() + interval 1 day

The first is safer for indexes.

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement