Date Difference in SQL Server

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:

READ ALSO  Window Server Hosting: What Dev Needs to Know
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.

READ ALSO  RFactor Server Hosting: A Comprehensive Guide for Devs

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!