SQL Functions   «Prev  Next»
Lesson 14 The DATEDIFF function
Objective Learn how to use the DATEDIFF function.

SQL datediff() Function

The DATEDIFF() function in SQL is used to calculate the difference between two dates. It returns the difference in the form of a specified time unit.
The syntax of the DATEDIFF() function is as follows:
DATEDIFF(unit, date1, date2)

Here, unit is the type of unit you want the function to return. This could be YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, SECOND, etc., depending on the specific SQL dialect you are using. date1 and date2 are the two dates you want to compare.
Let's say you have a employees table, and you want to find out the number of days between the hire_date and termination_date for each employee. Your SQL query would look like this:
SELECT
    employee_id,
    DATEDIFF(DAY, hire_date, termination_date) AS employment_duration
FROM
    employees;

This query will return a list of employee IDs along with the duration of their employment in days.
Do note that the order of the dates in the DATEDIFF() function matters. DATEDIFF(DAY, date1, date2) will return a positive number if date2 is after date1, and a negative number if date2 is before date1.
Lastly, the exact syntax and behavior of the DATEDIFF() function can vary between different SQL dialects, so always check your platform's SQL documentation for precise usage instructions.
If you want to determine what the difference is between two dates, you can use the DATEDIFF function. To use this function, indicate what segment of the date to compare, then provide the two dates. The segment of the date to compare comes from the intervals mentioned in the DATEADD function.
The syntax is:

SELECT DateDiff(interval, first date, second date)

So, to determine the difference in weeks between 5/2/1998 and 6/15/1998, you can use the following statement:
SELECT DateDiff(week, '5/2/1998', '6/15/1998')

The difference is 7 weeks.
Now try what is the difference in
  1. days?
  2. Months?
  3. Years?
You will quickly realize that the value returned is a rounded value, representing the nearest whole, integer value. So, for the number of months between the two dates, the result is 1.

Microsoft SQL Server


datediff() returns the number of specified time intervals between two dates

datediff() function in SQL

datediff() returns the number of specified time intervals between two dates
Use datediff() to count the days between two dates

datediff() returns the number of specified time intervals between two dates
Use datediff() to count the months between two dates