Javatpoint Logo
Javatpoint Logo

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.


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.

date_part name date_part abbreviation
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
hour hh
minute mi, n
second ss, s
millisecond ms
microsecond mcs
nanosecond ns

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.


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:

SQL Server DATEDIFF Function

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.

SQL Server DATEDIFF Function

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:

SQL Server DATEDIFF Function

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:

SQL Server DATEDIFF Function

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.


  • 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.

Youtube For Videos Join Our Youtube Channel: Join Now


Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Trending Technologies

B.Tech / MCA