Hey, folks! In this article, we will be focusing on SQL Server DATEDIFF() function in detail.
What is SQL DATEDIFF() function?
DATEDIFF() helps us estimate the difference between dates passed as a parameter to the function.
The function calculates the difference between the dates in terms of years, months, days, or time.
Syntax of SQL DATEDIFF() function
In order to operate the DATEDIFF() function, we need to follow the below command.
DATEDIFF(date_section, 'date1', 'date2');
date_section: This is the portion of the date and timestamp of which the comparison needs to be made.
The following date_section can be used for comparison:
| date_section |
|---|
| year |
| second |
| month |
| week |
| day |
| dayofyear |
| hour |
| minute |
| quarter |
| millisecond |
| microsecond |
| nanosecond |
The format of the date values passed is as follows
yyyy/mm/dd
Having understood the working of SQL DATEDIFF() function, lets us get started with the implementation of the same.
Examples of SQL Server DATEDIFF()
Let us now analyze the implementation and execution of the DATEDIFF() function.
SQL DATEDIFF() function with ‘year’ as an interval
SELECT DATEDIFF(year, '2020/5/20', '2025/10/15') AS Output;
In this example, we have passed ‘year’ as the interval and this leads to the difference between the years passed in the function parameter.
Output:
5
DATEDIFF() function with ‘month’ as an interval
SELECT DATEDIFF(month, '2020/5/20', '2020/10/15') AS Output;
The above example returns the difference between the months of the passed date intervals.
Output:
5
SQL DATEDIFF() function with ‘dayofyear’ as an interval
Example:
SELECT DATEDIFF(dayofyear, '2020/5/20', '2020/10/15') AS Output;
Output:
148
SQL DATEDIFF() function with ‘hour’ as an interval
The interval hour leads to calculate the difference between the hours of the two passed timestamps.
Example:
SELECT DATEDIFF(hour, '2020/5/20 08:00', '2020/5/20 15:00') AS Output;
Output:
7
SQL Server DATEDIFF() function with ‘week’ as an interval
The week interval calculates the number of weeks present between the two given timestamps.
Example:
SELECT DATEDIFF(week, '2020/5/20', '2020/6/20') AS Output;
Output:
4
Summary
- SQL Server DATEDIFF() function deals with the date and time values as parameters.
- Moreover, if we try to pass some non-date format values to the function, an error is raised by the compiler.
- The DATEDIFF() function is essential when we need to estimate the difference between different date_section of the timestamps.
Conclusion
By this, we have come to an end of this topic. I hope the entire topic is clear to all the readers. Please feel free to comment in case you come across a doubt.
For more such SQL related posts, please do visit SQL JournalDev.