SQL DATEDIFF

SQL DATEDIFF Function will find or Calculate the difference between Start Date and End Date. The SQL Datediff function always returns a signed integer value. For example, If you want to extract the year, month, or quarter from the existing Date, you can use this Datediff function.

SQL DATEDIFF Function Syntax

DATEDIFF(Datepart, Start Date, End Date)

Datepart: This is the part of a given date on which we are going to calculate the difference between the Start Date and End Date. The list of available datepart arguments in SQL Server for this DATEDIFF.

DatepartAbbreviationsDescription
yearyy,yyyyDisplay the difference between the Start and End date in terms of Years
quarterqq, qThis Datepart displays the difference between the Start and End date in terms of Quarters
monthmm, mDisplay the difference between the Start and End date in terms of Months
dayofyeardy, yDisplay the difference between the Start and End date in terms of Days of a Year (1 to 365)
daydd, dThis Datepart in Sql Server Datediff display the difference between the Start and End date in terms of Day (1 to 31)
weekwk, wwDisplay the difference between Start and End in terms of Week
weekdaydw, wDisplay the difference between Start and End in terms of Weekday
hourhhThis Datepart in it displays the difference between Start and End in terms of Hours
minutemi, nDisplay the difference between Start and End in terms of Minute
secondss, sThe SQL Datediff displays the difference between Start and End in terms of Seconds
millisecondmsDisplay the difference between the Start date and End in terms of Milliseconds
microsecondmcsThis Datediff argument displays the difference between the Start and End date in terms of Microsecond
nanosecondnsIt will print the difference between Start and End in terms of Nanoseconds.

Start: Please specify the valid date as the second argument (Starting Date). It can be a column, expression, or any SQL Server variable.

End: Please specify the valid date as the third argument (Ending Date). It can be a column, expression, or any variable.

SQL DATEDIFF Function Example

In this SQL Datediff example, we are going to declare two variables of datetime2 data type. Let us assign a valid date to both the variables and perform all the available operations.

First, We declared two variables, @ab, and @cd, and assigned the different dates and times to that variables.

Within the first statement (year, @ab, @cd), the SQL Datediff function prints the total number of years between the @ab variable and @cd

Next, we asked the function to display the total number of months between the @ab variable and the @cd variable.

SQL DATEDIFF 1

Example 2

In this example, we use the custom table to perform SQL Datediff operations on the Hire column and the built-in GETDATE() function.

The first statement in the 5th line will find the difference between the Hire Column and today in terms of Year.

In the next line, this Function will find the difference between the Hire Column and today in terms of Quarters. Similarly, you can select milliseconds, hours, minutes, a number of days, and days hours.

SQL DATEDIFF on Table Column 2