MySQL DATEDIFF: Calculate Days Between Two Dates

MySQL DATEDIFF: Calculate Days Between Two Dates

In this tutorial, We will discuss how to use the MySQL DATEDIFF function with different examples. In this article we will provide diffrent diffrent types of examples of the MySQL datediff.

MySQL DATEDIFF function

Basically the MySQL DATEDIFF function gives the difference between days between two date values.

Syntax

The MySQL DATEDIFF syntax is:

DATEDIFF( date1, date2 )

Params

  • date1 A date/datetime value
  • date2 A date/datetime value
Let’s take some MySQL DATEDIFF examples

The MySQL DATEDIFF is useful in many cases. For example, you can calculate an interval in those days that customers need to ship the products.

Example-1

When used with date values, only the date part is used to compare the date.

SELECT DATEDIFF('2019-10-10', '2019-02-15');


Output: 237

Example-2

When used with datatime values, only the date part is used to compare the date.

SELECT DATEDIFF('2019-5-10 11:41:14', '2019-02-15 12:10:08');

Output: 84

Example-3

Compare datetime and date values.

SELECT DATEDIFF('2019-4-10 11:41:14', '2019-02-15');

Output: 54

Must Know Date/Time Functions

List Of MySQL Date/Time Functions

Example-4

 Two more exmaple with dates

 mysql> SELECT DATEDIFF('2019-10-25', '2019-10-10');
 Output: 15

 mysql> SELECT DATEDIFF('2019-12-10', '2019-10-31');
 Output: 40

Example-5

The date should not be later than the second date on the first date. You can use the earlier date for the first argument and it will return a negative value.

 SELECT DATEDIFF('2018-12-31', '2019-10-10');

 Output: -283

Example-6

Here’s an example of using DATEDIFF() in a database query.

To calculate the number of days between created_at and updated_at of the users table, you use the DATEDIFF function as follows:

SELECT 
     user_id, 
     DATEDIFF(created_at, updated_at) daysDiff
 FROM
     users
 ORDER BY daysDiff DESC;

Conclusion

In this tutorial, you have learned how to use MySQL DATEDIFF function to calculate the number of days between two date values.

You may like

  1. DAY() MySQL Function – MySQL
  2. DAYNAME() Function Examples – MySQL
  3. DAYOFMONTH() MySQL Function – MySQL Tutorial
  4. WEEKOFYEAR() Function Examples – MySQL
  5. WEEKDAY() Function Examples – MySQL
  6. LAST_DAY MySQL Function Examples
  7. MySQL – DAYOFWEEK() Function Examples
  8. DAYOFYEAR() Function Examples – MySQL
  9. MySQL YEARWEEK() Function Examples – MySQL
  10. MySQL weekday function with example
  11. MySQL CURRENT TIMESTAMP Function Examples
  12. Get month name from date in MySQL
  13. mysql get first day of the current month
  14. mysql last day of previous month
  15. Get month number from month name in mysql
  16. mysql get day of week number from date
  17. Mysql Get Data Of Current Date, Week, Month, YEAR
  18. Query For Get Data Of Last Day, Week, Month, YEAR – Mysql

AuthorTuts Make

My name is Devendra Dode. I am a full-stack developer, entrepreneur, and owner of Tutsmake.com. I like writing tutorials and tips that can help other developers. I share tutorials of PHP, Javascript, JQuery, Laravel, Livewire, Codeigniter, Vue JS, Angular JS, React Js, WordPress, and Bootstrap from a starting stage. As well as demo example.

Leave a Reply

Your email address will not be published. Required fields are marked *