select data between two dates in mysql – MYSQL Query for Data between Two Dates Example – SELECT * FROM `table_name` WHERE active=0 AND CURDATE() between dateStart and dateEnd.
How to query between two dates in MySQL?
select data from database between two dates using MySQL – How to Calculate the Difference Between Two Dates in MySQL?
In the company table, there are main 3 columns: id, last_dt, and project. You’d like to calculate the difference between project and last_dt, or the total number of days from project to last_dt inclusively.
SELECT id, last_dt, project, DATEDIFF(project, last_dt) AS date_difference, DATEDIFF(project, last_dt) + 1 AS days_inclusive FROM company;
To count the difference between dates in MySQL, use the DATEDIFF(enddate, join_dt) function. The difference between join_dt and enddate is expressed in days. In this case, the enddate is project and the join_dt is last_dt.
SELECT members.* FROM members WHERE join_at >= '2023-12-01 00:00:00' AND join_at <= '2023-12-06 00:00:00'
Mysql: Select all data between two dates
SELECT id, name, join_at FROM `members` WHERE (join_at BETWEEN '2022-01-01 00:00:00' AND '2022-01-10 00:00:00')
Select Records Between Two Years
SELECT * FROM `member` WHERE YEAR(member_date) BETWEEN '2022' AND '2025'
Select Records Between Two Months
SELECT * FROM `member` WHERE MONTH(member_date) BETWEEN '05' AND '06'
Select All Records Between Two Months of Specific Year
SELECT * FROM `member` WHERE MONTH(member_date) BETWEEN '05' AND '06' AND YEAR(member_date) = '2025'
Select All Records of Specific Month
SELECT * FROM `member` WHERE MONTH(member_date) = '05'
Select All Records of Current Month
SELECT * FROM `member` WHERE MONTH(member_date) = MONTH(CURDATE())
Select All Records of Specific Year
SELECT * FROM `member` WHERE YEAR(member_date) = '2025'
Select All Records of Current Year
SELECT * FROM `member` WHERE YEAR(member_date) = YEAR(CURDATE())
Select All Records of Specific Month & Specific Year
SELECT * FROM `member` WHERE MONTH(member_date) = '05' AND YEAR(member_date) = '2025'
Date and Time Functions
| Function | Description |
|---|---|
| ADDDATE() | Add dates |
| ADDTIME() | Add time |
| CONVERT_TZ() | Convert from one timezone to another |
| CURDATE() | Returns the current date |
| CURTIME() | Returns the current system time |
| DATE_ADD() | Add two dates |
| DATE_FORMAT() | Format date as specified |
| DATE_SUB() | Subtract two dates |
| DATE() | Extract the date part of a date or datetime expression |
| DATEDIFF() | Subtract two dates |
| DAYNAME() | Returns the name of the weekday |
| DAYOFMONTH() | Returns the day of the month (1-31) |
| DAYOFWEEK() | Returns the weekday index of the argument |
| DAYOFYEAR() | Returns the day of the year (1-366) |
| EXTRACT | Extract part of a date |
| FROM_DAYS() | Convert a day number to a date |
| FROM_UNIXTIME() | Format date as a UNIX timestamp |
| GET_FORMAT() | Returns a date format string |
| HOUR() | Extract the hour |
| LAST_DAY | Returns the last day of the month for the argument |
| MAKEDATE() | Create a date from the year and day of year |
| MAKETIME | MAKETIME() |
| MICROSECOND() | Returns the microseconds from argument |
| MINUTE() | Returns the minute from the argument |
| MONTH() | Returns the month from the date passed |
| MONTHNAME() | Returns the name of the month |
| NOW() | Returns the current date and time |
| PERIOD_ADD() | Add a period to a year-month |
| PERIOD_DIFF() | Returns the number of months between two periods |
| QUARTER() | Returns the quarter from a date passed as an argument |
| SEC_TO_TIME() | Converts seconds to 'HH:MM:SS' format |
| SECOND() | Returns the second (0-59) |
| STR_TO_DATE() | Convert a string to a date |
| SUBTIME() | Subtract times |
| SYSDATE() | Returns the time at which the function executes |
| TIME_FORMAT() | Format as time |
| TIME_TO_SEC() | Returns the argument converted to seconds |
| TIME() | Extract the time portion of the expression passed as an argument |
| TIMEDIFF() | Subtract time |
| TIMESTAMP() | With a single argument, this function returns the Date or the Datetime expression. With two arguments, the sum of the arguments is returned |
| TIMESTAMPADD() | Add an interval to a datetime expression |
| TIMESTAMPDIFF() | Subtract an interval from a datetime expression |
| TO_DAYS() | Returns the date argument converted to days |
| UNIX_TIMESTAMP() | Returns a UNIX timestamp to a format acceptable to MySQL |
| UTC_DATE() | Returns the current Universal Time (UTC) date |
| UTC_TIME() | Returns the current Universal Time (UTC time |
| UTC_TIMESTAMP() | Returns the current Universal Time (UTC) date and time |
| WEEK() | Returns the week number |
| WEEKDAY() | Returns the weekday index |
| WEEKOFYEAR() | Returns the calendar week of the date (1-53) |
| YEAR() | Returns the year |
| YEARWEEK() | Returns the year and week |
I hope you get an idea about select data between two dates in mysql.
I would like to have feedback on my infinityknow.com.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.