SQL DATEDIFF function is used to find or Calculate the difference between Start Date and End Date. This function always return signed integer value. For example, If you want to extract year, month or quarter from the existing Date, you can use this SQL Datediff function.
SQL DATEDIFF Syntax
|
1 |
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 Start Date and End Date. Following table will display the list of available datepart argument in SQL Server 2014
| Datepart | Abbreviations | Description |
|---|---|---|
| year | yy,yyyy | Display the difference between Start and End date in terms of Years |
| quarter | qq, q | Display the difference between Start and End date in terms of Quarters |
| month | mm, m | Display the difference between Start and End date in terms of Months |
| dayofyear | dy, y | Display the difference between Start and End date in terms of Days of a Year (1 to 365) |
| day | dd, d | Display the difference between Start and End date in terms of Day (1 to 31) |
| week | wk, ww | Display the difference between Start and End date in terms of Week |
| weekday | dw, w | Display the difference between Start and End date in terms of Week day |
| hour | hh | Display the difference between Start and End date in terms of Hours |
| minute | mi, n | Display the difference between Start and End date in terms of Minute |
| second | ss, s | Display the difference between Start and End date in terms of Seconds |
| millisecond | ms | Display the difference between Start and End date in terms of Milliseconds |
| microsecond | mcs | Display the difference between Start and End date in terms of Microsecond |
| nanosecond | ns | Display the difference between Start and End date in terms of Nanosecond |
Start Date: Please specify the valid date as second argument (Starting Date). It can be column, expression or any variable.
End Date: Please specify the valid date as third argument (Ending Date). It can be column, expression or any variable.
SQL DATEDIFF Function Example 1
In this example we are going to declare two variables of datetime2 data type. Let us assign valid date to both the variables and perform all the available SQL datediff operation
SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
DECLARE @Date1 datetime2 = '2015-01-01 09:12:54.1234567', @Date2 datetime2 = '2015-08-25 14:24:04.1234567' SELECT 'YEAR' AS [SQL DATEDIFF], DATEDIFF(year, @Date1, @Date2) AS [Values] UNION ALL SELECT 'QUARTER', DATEDIFF(quarter, @Date1, @Date2) UNION ALL SELECT 'MONTH', DATEDIFF(month, @Date1, @Date2) UNION ALL SELECT 'DAYOFYEAR', DATEDIFF(dayofyear, @Date1, @Date2) UNION ALL SELECT 'DAY', DATEDIFF(day, @Date1, @Date2) UNION ALL SELECT 'WEEK', DATEDIFF(week, @Date1, @Date2) UNION ALL SELECT 'WEEKDAY', DATEDIFF(weekday, @Date1, @Date2) UNION ALL SELECT 'HOUR', DATEDIFF (hour, @Date1, @Date2) |
OUTPUT
ANALYSIS
First, We declared two variable @Date1, @Date2 and assigned the different date and time to that variables
|
1 2 |
DECLARE @Date1 datetime2 = '2015-01-01 09:12:54.1234567', @Date2 datetime2 = '2015-08-25 14:24:04.1234567' |
We asked the SQL Datediff function to display the total number of years between @Date1 variable and @Date2
|
1 |
SELECT 'YEAR', DATEDIFF(year, @Date1, @Date2) |
We asked the SQL Datediff function to display the total number of months between @Date1 variable and @Date2
|
1 |
SELECT 'MONTH', DATEDIFF(month, @Date1, @Date2) |
and so on
SQL DATEDIFF Function Example 2
In this example, we are going to use one of the custom SQL table to perform SQL Datediff operations on Hire Date column and built-in sql function GETDATE().
SQL CODE
|
1 2 3 4 5 6 7 8 9 |
SELECT [FirstName] + ' '+ [LastName] AS [Full Name] ,[Occupation] ,[YearlyIncome] ,[HireDate] ,DATEDIFF (year, [HireDate], GETDATE()) AS [YEARS] ,DATEDIFF (quarter, [HireDate], GETDATE()) AS [QUARTERS] ,DATEDIFF (month, [HireDate], GETDATE()) AS [MONTHS] ,DATEDIFF (day, [HireDate], GETDATE()) AS [DAYS] FROM [SQL Tutorial].[dbo].[Employee] |
OUTPUT
ANALYSIS
Below statement will find the difference between Hire Date Column and today in terms of Year
|
1 |
DATEDIFF (year, [HireDate], GETDATE()) AS [YEARS] |
Below statement will find the difference between Hire Date Column and today in terms of Quarters
|
1 |
DATEDIFF (quarter, [HireDate], GETDATE()) AS [QUARTERS] |
Thank You for Visiting Our Blog
Leave a Reply