Back to list MySQL How to Find the Number of Days Between Two Dates in MySQL Database: MySQL Operators:DATEDIFF() Problem: You’d like to get the difference, in days, between two dates in a MySQL database. Example: Our database has a table named food with data in the columns id, name, purchase_date, and expiration_date. idnamepurchase_dateexpiration_date 1bread2019-07-202019-08-22 2butter2018-07-302019-08-10 3milk2019-01-122019-01-13 4yogurt2019-02-252019-02-24 For each food product, let’s get the name of the product and the number of days between its expiration and purchase dates. Solution: We’ll use the DATEDIFF() function. Here’s the query you would write: SELECT name, DATEDIFF(expiration_date, purchase_date) AS days FROM food; Here’s the result of the query: idnamepurchase_dateexpiration_date 1bread2019-07-202019-08-22 2butter2018-07-302019-08-10 3milk2019-01-122019-01-13 4yogurt2019-02-252019-02-24 Discussion: Use the DATEDIFF() function to retrieve the number of days between two dates in a MySQL database. This function takes two arguments: The end date. (In our example, it’s the expiration_date column.) The start date. (In our example, it’s the purchase_date column.) These arguments can be date/datetime values, expressions that return date/datetime values, or columns of the datetime or date data type. This function subtracts the start date from the end date and returns the number of days as an integer. In our example, butter was purchased on ‘2018-07-30’, but its expiration date was ‘2019-08-10’; after purchase, it could be eaten during 376 days. Notice that yogurt was purchased as an outdated product: the difference in days is -1 and its purchase date is later than its expiration date. Recommended courses: SQL Basics Standard SQL Functions SQL Practice Set Recommended articles: 18 Useful Important SQL Functions to Learn ASAP Performing Calculations on Date- and Time-Related Values How Often Employees Are Running Late for Work: SQL Datetime and Interval SQL Arithmetic See also: How to Get the Date from a Datetime Column in MySQL How to Get the Current Date and Time in MySQL How to Add Days to a Date in MySQL How to Add Time to a Datetime Value in MySQL How to Change Seconds to a Time Value in MySQL Tags: MySQL Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.