SQL Server DATEDIFF() Function
FREE
DATEDIFF() offers a streamlined approach to measure intervals between dates, a fundamental capability in data analysis and reporting. By specifying a time unit and two dates, users can effortlessly quantify periods, enhancing the efficiency of temporal evaluations.
What is the DATEDIFF() function?
The DATEDIFF() function is a powerful date manipulation tool used to calculate the difference between two dates. It is designed to work across various platforms, providing flexibility in handling date-related data. The function is mainly known for its precision in measuring time spans by requiring three essential parameters: the unit of measurement for the interval (such as days, months, or years), and the two dates (start and end) for which the difference is to be calculated.
The function is widely supported across different database systems, making it a standard choice for date calculations. It is especially compatible with systems like Azure SQL and Analytics Platform System, starting from versions as early as SQL Server 2008.
DATEDIFF() is predominantly used within data manipulation functions. Its ability to break down time intervals into specific units makes it invaluable for tasks requiring detailed time-based analysis, reporting, or any scenario where understanding the duration between dates is crucial.
Syntax Of DATEDIFF()
Parameter of DATEDIFF()
The DATEDIFF() function requires three parameters to operate:
- interval: Defines the unit of time for the difference calculation between the two dates. It specifies the granularity of the result, such as years, months, days, etc.
The interval parameter accepts various values to cater to different levels of time granularity:
| Interval | Abbreviations | Description |
|---|---|---|
| year | yyyy, yy, y | Calculates difference in full calendar years. |
| quarter | qq, q | Determines the number of quarters between dates. |
| month | mm, m | Computes the number of months between dates. |
| dayofyear | - | Finds the difference in days since the start of the year. |
| day | dd, d | Calculates the total days between dates. |
| week | ww, wk | Measures the number of weeks between dates. |
| weekday | dw, w | Counts the weekdays between dates. |
| hour | hh | Computes the hours difference between times. |
| minute | mi, n | Calculates the minutes difference. |
| second | ss, s | Determines the seconds difference. |
| millisecond | ms | Measures the milliseconds difference. |
-
start_date: The initial date from which the calculation begins. It acts as the baseline for the interval calculation.
-
end_date: The final date up to which the difference is calculated. It marks the endpoint of the interval.
Return Type Of DATEDIFF()
DATEDIFF in SQL Server returns an integer representing the difference between date1 and date2 with the unit specified by the interval.
If the returned result is out of range for 32-bit integer(-2,147,483,648 to +2,147,483,647), then DATEDIFF in SQL Server returns an error. Therefore in the above case, one should use the DATEDIFF_BIG() function.
Example of DATEDIFF()
Example 1: Calculate the number of days it took for orders to be delivered, from the order date to the delivery date.
Dummy Data Set:
| order_id | order_date | delivery_date |
|---|---|---|
| 1001 | 2022-07-01 | 2022-07-04 |
| 1002 | 2022-07-02 | 2022-07-05 |
| 1003 | 2022-07-03 | 2022-07-06 |
Solution:
| order_id | delivery_days |
|---|---|
| 1001 | 3 |
| 1002 | 3 |
| 1003 | 3 |
Example 2: Determine the tenure of employees in years based on their start date up to the current date.
Dummy Data Set:
| employee_id | start_date |
|---|---|
| E001 | 2015-01-10 |
| E002 | 2017-03-15 |
| E003 | 2019-06-20 |
Solution: Assuming the current date is 2023-02-15,
| employee_id | years_of_service |
|---|---|
| E001 | 8 |
| E002 | 6 |
| E003 | 4 |
Example 3: Analyze the month-over-month sales growth by comparing the sales figures from the current month to the previous month.
Dummy Data Set:
| sale_date | sales_amount |
|---|---|
| 2022-05-01 | 5000 |
| 2022-06-01 | 5500 |
| 2022-07-01 | 6000 |
Solution:
| sale_year | sale_month | sales_amount | previous_month_sales | growth |
|---|---|---|---|---|
| 2022 | 5 | 5000 | NULL | NULL |
| 2022 | 6 | 5500 | 5000 | 500 |
| 2022 | 7 | 6000 | 5500 | 500 |
Example 4: Identify the ageing of unresolved support tickets by calculating the number of days since they were opened.
Dummy Data Set:
| ticket_id | opened_date | status |
|---|---|---|
| T01 | 2023-01-01 | Open |
| T02 | 2023-01-15 | In-Progress |
| T03 | 2023-02-01 | Open |
Solution: Assuming the current date is 2023-02-15,
| ticket_id | days_open |
|---|---|
| T01 | 45 |
| T02 | 31 |
| T03 | 14 |
Conclusion
- The DATEDIFF function in SQL Server is used to calculate the difference between two dates.
- The DATEDIFF function accepts three parameters, interval, date1, and date2.
- This function will return an integer value that represents difference between date1 and date2 with the unit specified by the interval.
- If the returned result is out of range for 32-bit integer (-2,147,483,648 to +2,147,483,647), then DATEDIFF in SQL Server returns an error.