How do I query between two dates using MySQL?

Getting date formats right

For MySQL, the 'YYYY-MM-DD' format ensures accurate comparison during date queries. If daytime precision isn't necessary, leverage DATE() to only return the date part from a datetime field:

SELECT * FROM your_table WHERE DATE(`your_datetime`) BETWEEN 'start_date' AND 'end_date';
// Wow! We just time-travelled, stripped off time and kept only the date! Neat, huh?


If you require full time precision, use the greater than or equal to >= and less than or equal to <= comparison operators:

SELECT * FROM your_table WHERE `your_datetime` >= 'start_date 00:00:00' AND `your_datetime` <= 'end_date 23:59:59';


Dealing with date data

Correct date field types

It's crucial to verify your date_field is a DATE or DATETIME type. Use the following command to check:

SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'your_table' AND column_name = 'your_date';
// It's like a first date, just getting to know each other 😏


Transforming string to date

If date strings are your hurdles, STR_TO_DATE is your pole vault. Use it to convert strings into a date format MySQL understands:

SELECT * FROM your_table 
WHERE `your_date` BETWEEN STR_TO_DATE('start_str_date','%d,%m,%Y') AND STR_TO_DATE('end_str_date','%d,%m,%Y');
// No more string theories, just pure dates!


Extracting date parts

You may occasionally uncover treasure by extracting specific date parts with the EXTRACT() function:

SELECT * FROM your_table
WHERE EXTRACT(YEAR_MONTH FROM `your_date`) = '202301';
// Feeling like a dentist, extracting parts from dates 😅


Mastering date ranges

Beware the ides of BETWEEN

Given BETWEEN is inclusive, for exclusive ranges, modify your conditions:

SELECT * FROM your_table
WHERE `your_date` > 'start_date' AND `your_date` < 'end_date';
// Like an exclusive movie premiere, we're keeping out the start and end dates. Fancy, huh?


Test. Test. Genius!

Remember to test your query on various known date ranges, maintaining accuracy:

SELECT COUNT(*) FROM your_table WHERE `your_date` BETWEEN 'start_known_date' AND 'end_known_date';
// It's just like a dress rehearsal, but for SQL queries.


Debugging the unexpected

If your output feels off:

• Switcharoo?: Inspect if your start date is less than your end date.
• Ghost data?: Double-check for data existence in your query range.
• Wrong match?: Avoid accidental pattern matching in your date conditions.