Select records between a date in d-mY format and 1rst of next month, plus 1 day in Mysql

Question

I need to select all data from a table 'followup' between followup_date and first of next month (including 1rst of next month). The format of my date in DB (obtained from an API) is d-m-Y.

The follow-up date for example is: 18-07-2020.

I have the following query:

SELECT * from followup 
WHERE DATEDIFF(STR_TO_DATE(`followup_date`,'%d-%m-%Y'), DATE_FORMAT(CURDATE() + INTERVAL 1 MONTH,'%Y-%m-01'))- 1 < 0 ;

I am getting -15 days as difference and getting records correctly, including 1rst. Is the query correct and efficient and will it work correctly for all months.

Requesting suggestions from experts for improvements, if any.

A couple of options: either store dates using a date data type, or don't bother with an RDBMS — Strawberry, Jul 04 '20 at 07:20

Nick · Accepted Answer · 2020-07-04T08:16:12.303

1

You should convert your followup_date column to a DATE type. You can then make your query sargable by removing the function calls on followup_date and simply comparing it with the target date:

SELECT * from followup 
WHERE `followup_date` <= DATE_FORMAT(CURDATE() + INTERVAL 1 MONTH,'%Y-%m-01')

I suspect adding one day to LAST_DAY(CURDATE()) might be more efficient:

SELECT * from followup 
WHERE `followup_date` <= LAST_DAY(CURDATE()) + INTERVAL 1 DAY

Demo on SQLFiddle

edited Jul 04 '20 at 08:16

answered Jul 04 '20 at 07:33

Nick

138,499
22
57
95

@Pamela sorry about that - I have fixed both. – Nick Jul 04 '20 at 08:18
@Pamela I've added a demo of both queries – Nick Jul 04 '20 at 08:20
So the second query is more efficient. Execution Time: 1ms compared with 8ms for first . Thank you so much.. – Pamela Jul 04 '20 at 08:22
1

@Pamela that's interesting to know that a lot of the slowdown was in the `DATE_FORMAT`. I'll keep it in mind for future queries. – Nick Jul 04 '20 at 08:24

Select records between a date in d-mY format and 1rst of next month, plus 1 day in Mysql

1 Answers1