SQL Server Datediff()

Dates are important when working with databases. They allow you to include a specific timestamp for data.

Using this guide, you will learn how to calculate the difference between two dates using the SQL Server datediff() function.

Basic Usage

The datediff function allows you to pass start and end date values. It then calculates and returns the difference between them in years, months, weeks, days, etc.

The function syntax is as:

datediff(unit, start_date, end_date);

Function Arguments

The function three major arguments expressed as:

1. Unit – Represents the units under which the function will report the difference between the specified start and end date. SQL Server does not support passing the value of the unit parameter as a variable of a string such as ‘year’;

The table below shows the unit and abbreviation you can use in the unit parameter.

unit                                               abbreviation
————————————————– ————
nanosecond                                         ns
microsecond                                        mcs
millisecond                                        ms
SECOND                                             s,ss
MINUTE                                             mi,n
HOUR                                               hh
week                                               wk, ww
DAY                                                dd, d
DAY OF YEAR,                                       dy, y
MONTH                                              mm, m
quarter                                            qq, q
YEAR                                               yy, yyyy

2. start_date & end_date – defines the two dates whose difference must be calculated. The value of either argument can be a literal string or an expression that can resolve to a value of type: date, datetime, datetim2, datetimeoffset, time, smalldatetime.
To avoid ambiguity, consider using four digits to represent a year.

Function Return Value

The datediff function returns an int type, representing the start and end date difference. Sometimes, the difference might be out of range for the size of an integer. If that happens, the datediff function will return an error. Consider using the datediff_big function for such a scenario.

SQL Server Datediff() Examples

The following examples are useful to illustrate how to work with the datediff() function.

Example 1:

Consider the example shown below:

SELECT DATEDIFF(MONTH, ‘1998-09-06’, ‘2022-06-06’) AS difference;

The query above should return the total month difference between the start and end date as:

difference
———–
285
(1 ROW affected)

Example 2:

Suppose you want to calculate how many seconds are there in a day? You can use the datediff function as shown:

SELECT DATEDIFF(SECOND, ‘2022-01-01’, ‘2022-01-02’) AS difference;

The example above should return the number of seconds in one day as:

———–
86400
(1 ROW affected)

Example 3:

You may encounter a scenario where the difference between two dates is large than the maximum for an integer type. For example, the difference in nanoseconds in one day.

Consider the example query below:

SELECT DATEDIFF(nanosecond, ‘2022-01-01’, ‘2022-01-02’) AS difference;

If we run the above query, SQL Server returns an overflow error as:

The datediff FUNCTION resulted IN an overflow. The NUMBER OF dateparts separating two DATE/TIME instances IS too LARGE. Try TO USE datediff WITH a less precise datepart.

To resolve this, use the datediff_big function as:

SELECT datediff_big(nanosecond, ‘2022-01-01’, ‘2022-01-02’) AS difference;

In this example, the query returns the number of nanoseconds in one day as:

difference
——————–
86400000000000

Example 4:

The example below shows you how to calculate the difference in hours between 1998 and 2021.

 SELECT datediff(HOUR, ‘1998-01-01’, ‘2021-01-01’);

The resulting value is as:

———–
201624

Example 5:

Since the return value of the datediff function is an integer, we can cast it to a string, allowing you to perform string concatenation.

For example:

SELECT CAST(datediff(HOUR, ‘1998-01-01’, ‘2021-01-01’) AS VARCHAR(20)) + ‘ hours’;

The example above casts the value from an integer string and adds a string concatenation.

The example result is as:

————————–
201624 hours

Final Words

We hope you enjoyed and learned how to calculate the differences between two dates using various units in SQL Server. The examples provided in this guide can help you manipulate date and time data more efficiently.

Thanks for reading!

Leave a Reply

Your email address will not be published. Required fields are marked *