How to Select With DATEDIFF in MySQL

Written by andy carr
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Select With DATEDIFF in MySQL
The DATEDIFF MySQL function calculates the difference in two dates. (Thinkstock/Comstock/Getty Images)

DATEDIFF is one of the date/time functions supplied with MySQL to calculate time periods between dates. The DATEDIFF function calculates the number of days between two dates, but this can be converted into other units using MySQL functions available in a select statement. The syntax of a select statement using DATEDIFF is "Select Datediff ('2011-05-05','2011-05-04')," where the first date represents a new date, and the second date represents an old (usually a previous) date. The dates can be reversed to show negative numbers.

Skill level:
Easy

Other People Are Reading

Instructions

  1. 1

    Go to a MySQL prompt, which should be displayed as follows:

    MySQL>

    From here the DATEDIFF command can be tested on the command line.

  2. 2

    Create a select statement using DATEDIFF with a single day between them. An example is as follows:

    SELECT DATEDIFF('2011-10-24','2011-10-23') AS DifferenceDate

    This will display:

    DifferenceDate


    1

  3. 3

    Swap the select statement round using DATEDIFF with a single day between them. An example is as follows:

    SELECT DATEDIFF('2011-10-23','2011-10-24') AS DifferenceDate

    This will display:

    DifferenceDate


    -1

    This results in a negative day results (i.e., the new date was before the old date).

  4. 4

    Enter the same date for the new and old date as follows:

    SELECT DATEDIFF('2011-10-24','2011-10-24') AS DifferenceDate

    This will display:

    DifferenceDate


    0

    As no days have passed either way, the result is 0.

  5. 5

    Calculate how many days have occurred in a year as follows:

    SELECT DATEDIFF('2011-10-24','2010-10-24') AS DifferenceDate

    This will display:

    DifferenceDate


    365

    As this is 365 days earlier.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.