SQL Server DATEDIFF Function will find or Calculate the difference between Start Date and End Date. The 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.
| Datepart | Abbreviations | Description |
|---|---|---|
| year | yy,yyyy | Display the difference between the Start and End date in terms of Years |
| quarter | qq, q | This Datepart displays the difference between the Start and End date in terms of Quarters |
| month | mm, m | Display the difference between the Start and End date in terms of Months |
| dayofyear | dy, y | Display the difference between the Start and End date in terms of Days of a Year (1 to 365) |
| day | dd, d | This Datepart in Sql Server Datediff display the difference between the Start and End date in terms of Day (1 to 31) |
| week | wk, ww | Display the difference between Start and End in terms of Week |
| weekday | dw, w | Display the difference between Start and End in terms of Weekday |
| hour | hh | This Datepart in it displays the difference between Start and End in terms of Hours |
| minute | mi, n | Display the difference between Start and End in terms of Minute |
| second | ss, s | It displays the difference between Start and End in terms of Seconds |
| millisecond | ms | Display the difference between the Start date and End in terms of Milliseconds |
| microsecond | mcs | This Datediff argument displays the difference between the Start and End date in terms of Microsecond |
| nanosecond | ns | It 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 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.
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.
