SQL Server DATEDIFF Function

DATEDIFF function in SQL Server is a basic function used to perform mathematical calculations based on dates. It produces the output as an integer value in date units given as years, months, days, minutes, and seconds. The DATEDIFF function's official definition is to calculate the difference between two dates.

This function has the following features:

• It is used to find the difference between two dates.
• It belongs to the Date Functions category.
• The interval, the first value of date, and the second value of dates are the three parameters accepted by this function.
• Time can be included in the interval portion as well as the date value section of this function.

Syntax

The following are the syntax to illustrate the DATEDIFF function:

The parameters of this function are explained below:

date_part: It is a specified part of a date, e.g., a year, a quarter, a month, or a week that we need to compare the start and end date values. It cannot be specified in a variable or string like 'month'.

The table below shows all of the valid date_part values. It should be either the full name of the date_part or any stated abbreviation of the complete name. They return the same value.

start_date and end_date: These are the specified dates to be compared. It can resolve to one of the following values DATE, DATETIME, DATETIMEOFFSET, DATETIME2, SMALLATETIME, or TIME.

Return Type

This function returns an integer value in the unit specified by the date_part argument.

Example

Let us understand how the DATEDIFF function works in SQL Server with different examples.

1. The below example uses the DATEDIFF function and compare the difference between two dates in the several date parts:

Executing the above statement will return the following output:

2. The below example uses the DATEDIFF function and takes the scalar system functions as arguments for start date and end date and then compares their differences.

Executing the above statement will return the below output. Its output can be different in your system.

3. This example will use the DATEDIFF function to explain their working with table columns. First, we will create an employee_info table using the below statement:

Next, we will insert some values into this table as follows:

Executing the SELECT statement will return the following output:

Now, we will calculate the duration for each employee in the table with the help of the DATEDIFF function. See the table before and after using the DATEDIFF query:

It will return the below output:

We can see that by using the GETDATE() function, the current date is displayed in a different column for each employee. The number of days, months, and years is then calculated by subtracting this date from the joining date.

Summary

• The DATEDIFF() function deals with the date and time values as arguments.
• The compiler raises an error if we try to give non-date format variables to the function arguments.
• This function is useful when we need to find the difference between different date sections of timestamps.