Hello Dev! In this article, we will take a deep dive into the topic of date difference in SQL Server. We will explore the different ways to calculate the difference between two dates in SQL Server and how to format the results. So, whether you are a beginner or an experienced SQL developer, this article has something for everyone.
Understanding Date and Time Data Types
Before we dive into the topic of date difference, let’s review the date and time data types in SQL Server. SQL Server supports two types of date and time data types: datetime and smalldatetime. The datetime data type stores the date and time values from January 1, 1753, to December 31, 9999, with an accuracy of 3.33 milliseconds. On the other hand, the smalldatetime data type stores the date and time values from January 1, 1900, to June 6, 2079, with an accuracy of one minute.
Both datetime and smalldatetime store date and time values as a binary number. When you retrieve the value, SQL Server converts the binary value to a human-readable format. Now that we are familiar with the date and time data types let’s move on to calculating the difference between two dates.
Calculating Date Difference Using DATEDIFF Function
The easiest and most common way to calculate the difference between two dates in SQL Server is by using the DATEDIFF function. The DATEDIFF function returns the difference between two date values in a specified date part. Let’s take a look at the syntax of the DATEDIFF function:
Function |
Description |
|---|---|
DATEDIFF(datepart, startdate, enddate) |
Returns the difference between two dates in a specified date part. |
Datepart Values
The first parameter of the DATEDIFF function specifies the date part you want to use to calculate the difference between two dates. The table below shows the different date part values you can use:
Datepart Value |
Description |
|---|---|
year |
Returns the number of years between two dates. |
quarter |
Returns the number of quarters between two dates. |
month |
Returns the number of months between two dates. |
dayofyear |
Returns the number of days between two dates, based on a 365-day year. |
day |
Returns the number of days between two dates. |
week |
Returns the number of weeks between two dates. |
weekday |
Returns the number of weekdays between two dates. |
hour |
Returns the number of hours between two dates. |
minute |
Returns the number of minutes between two dates. |
second |
Returns the number of seconds between two dates. |
millisecond |
Returns the number of milliseconds between two dates. |
Examples
Let’s look at some examples of using the DATEDIFF function:
Example 1: Calculate the difference between two dates in years.
Query |
Result |
|---|---|
SELECT DATEDIFF(year, ‘2020-01-01’, ‘2022-01-01’) AS DateDiff; |
2 |
Example 2: Calculate the difference between two dates in months.
Query |
Result |
|---|---|
SELECT DATEDIFF(month, ‘2020-01-01’, ‘2022-01-01’) AS DateDiff; |
24 |
Example 3: Calculate the difference between two dates in days.
Query |
Result |
|---|---|
SELECT DATEDIFF(day, ‘2020-01-01’, ‘2022-01-01’) AS DateDiff; |
731 |
Example 4: Calculate the difference between two dates in hours.
Query |
Result |
|---|---|
SELECT DATEDIFF(hour, ‘2020-01-01 12:00:00’, ‘2020-01-01 16:00:00’) AS DateDiff; |
4 |
Example 5: Calculate the difference between two dates in minutes.
Query |
Result |
|---|---|
SELECT DATEDIFF(minute, ‘2020-01-01 12:00:00’, ‘2020-01-01 12:30:00’) AS DateDiff; |
30 |
Example 6: Calculate the difference between two dates in seconds.
Query |
Result |
|---|---|
SELECT DATEDIFF(second, ‘2020-01-01 12:00:00’, ‘2020-01-01 12:00:10’) AS DateDiff; |
10 |
Calculating Date Difference Using Datepart Functions
In addition to using the DATEDIFF function, you can also calculate the difference between two dates by using the datepart functions. The datepart functions return an integer that represents a specific part of a date and time value. The syntax of the datepart functions is as follows:
Function |
Description |
|---|---|
DATEPART(datepart, date) |
Returns an integer that represents a specific part of a date and time value. |
Examples
Let’s look at some examples of using the datepart functions:
Example 1: Calculate the difference between two dates in years.
Query |
Result |
|---|---|
SELECT DATEPART(year, ‘2022-01-01’) – DATEPART(year, ‘2020-01-01’) AS DateDiff; |
2 |
Example 2: Calculate the difference between two dates in months.
Query |
Result |
|---|---|
SELECT (DATEPART(year, ‘2022-01-01’) – DATEPART(year, ‘2020-01-01’)) * 12 + DATEPART(month, ‘2022-01-01’) – DATEPART(month, ‘2020-01-01’) AS DateDiff; |
24 |
Example 3: Calculate the difference between two dates in days.
Query |
Result |
|---|---|
SELECT DATEDIFF(day, ‘2020-01-01’, ‘2022-01-01’) AS DateDiff; |
731 |
Formatting Date Difference
Now that we know how to calculate the difference between two dates, let’s look at how to format the results. The simplest way to format the date difference is to use the CONVERT function. The CONVERT function converts a value to a specified data type. Let’s take a look at the syntax:
Function |
Description |
|---|---|
CONVERT(data_type, expression, style) |
Converts a value to a specified data type. |
The third parameter of the CONVERT function, the style parameter, determines the format of the result. The table below shows the different style values you can use:
Style Value |
Description |
|---|---|
1 |
Returns the date in the format yyyy-mm-dd. |
3 |
Returns the date in the format dd/mm/yy. |
4 |
Returns the date in the format dd.mm.yyyy. |
101 |
Returns the date in the format mm/dd/yyyy. |
102 |
Returns the date in the format yyyy.mm.dd. |
103 |
Returns the date in the format dd/mm/yyyy. |
104 |
Returns the date in the format dd.mm.yyyy. |
106 |
Returns the date in the format dd mon yyyy. |
107 |
Returns the date in the format mon dd, yyyy. |
110 |
Returns the date in the format mm-dd-yyyy. |
111 |
Returns the date in the format yyyy/mm/dd. |
113 |
Returns the date in the format dd mon yyyy hh:mi:ss. |
114 |
Returns the date in the format hh:mi:ss:mmm. |
Examples
Let’s look at some examples of formatting the date difference:
Example 1: Format the date difference in years.
Query |
Result |
|---|---|
SELECT CONVERT(varchar(10), DATEDIFF(year, ‘2020-01-01’, ‘2022-01-01’)) + ‘ years’ AS DateDiff; |
2 years |
Example 2: Format the date difference in months.
Query |
Result |
|---|---|
SELECT CONVERT(varchar(10), DATEDIFF(month, ‘2020-01-01’, ‘2022-01-01’)) + ‘ months’ AS DateDiff; |
24 months |
Example 3: Format the date difference in days.
Query |
Result |
|---|---|
SELECT CONVERT(varchar(10), DATEDIFF(day, ‘2020-01-01’, ‘2022-01-01’)) + ‘ days’ AS DateDiff; |
731 days |
FAQ
Q: Can I use the DATEDIFF function with datetime2 data type?
A: Yes, the DATEDIFF function can be used with the datetime2 data type.
Q: What is the maximum difference that can be calculated using the DATEDIFF function?
A: The DATEDIFF function can calculate the difference between two dates up to 68 years, 19 days, 3 hours, 14 minutes, and 7 seconds. If the difference is greater than that, you will need to use a different method to calculate the difference.
Q: Can I use the CONVERT function to format the date difference in seconds?
A: Yes, you can use the CONVERT function to format the date difference in seconds. Use the style value 114, which returns the date in the format hh:mi:ss:mmm.
Q: Can I use the datepart functions to calculate the difference between two smalldatetime values?
A: Yes, the datepart functions can be used to calculate the difference between two smalldatetime values.
Q: What is the accuracy of the DATEDIFF function?
A: The DATEDIFF function has an accuracy of one millisecond.
That’s it for this article, Dev! We hope you found it helpful and informative. If you have any questions or comments, feel free to leave them below. Happy coding!