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:
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.
This function returns an integer value in the unit specified by the date_part argument.
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.