DATEDIFF SQL Server function

The DATEDIFF SQL Server function allows to calculate the difference between two values of date and time in units of a certain element (i.e. year, month, day), it takes into account only a certain element and higher elements in the time hierarchy – but not lower elements.

Syntax of the DATEDIFF function in SQL Server (Transact-SQL)

DATEDIFF( interval, date1, date2 )

Parameters or arguments

interval – time interval for calculating the difference between date1 and date2. This could be one of the following values:

date1, date2 – two dates to calculate the difference between them.

Application
The DATEDIFF function can be used in future versions of SQL Server (Transact-SQL):
SQL Server vNext, SQL Server 2016, SQL Server 2015, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

Example
Let’s look at some examples of the SQL Server function DATEDIFF to understand how to use the DATEDIFF function in SQL Server (Transact-SQL). For example:

SELECT DATEDIFF(year, '25.12.2015', '25.12.2017');
-Result: 2

SELECT DATEDIFF(yyyy, '25.12.2015', '25.12.2017');
-Result: 2

SELECT DATEDIFF(yy, '25.12.2015', '25.12.2017');
-Result: 2

SELECT DATEDIFF(month, '01.09.2017', '25.12.2017');
-Result: 3

SELECT DATEDIFF(day, '01.09.2017', '25.12.2017');
-Result: 115

SELECT DATEDIFF(hour, '25.12.2017 08:00', '25.12.2017 10:45');
-Result: 2

SELECT DATEDIFF(minute, '25.12.2017 08:00', '25.12.2017 10:45');
-Result: 165

Syntax:

DATEDIFF(datepart, startdate, enddate)

The function returns the time interval between two time stamps – startdate and enddate. This interval can be measured in different units. The possible options are determined by the datepart argument and are listed above for the DATEADD function.

Example 2

Determine the number of days that have passed between the first and last completed flight.

SELECT DATEDIFF(dd, (SELECT MIN(date)
FROM pass_in_trip
),
(SELECT MAX(date)
FROM pass_in_trip
)
);

Example 3

Determine the duration of flight 1123 in minutes.

Please note here that the departure time (time_out) and arrival time (time_in) are stored in the datetime fields of the Trip table type. As already mentioned, SQL Server up until version 2005 did not have separate temporal data types for date and time. That’s why if you insert only time into the datetime field (for example, UPDATE trip SET time_out = ’17:24:00′ WHERE trip_no = 1123), the time will be added with the default date value (1900-01-01), which is the starting point of time.

Requiring solution:

SELECT DATEDIFF(mi, time_out, time_in) dur
FROM trip
WHERE trip_no = 1123;

(which gives 760 minutes) is wrong for two reasons.

First, for flights that depart one day and arrive the next, the value calculated in this way will be incorrect;

Secondly, it is unreliable to make any assumptions about a day that is present only because of the need to match the type of datetime.

But how do you know if the plane landed the next day? Here helps to describe the subject area, which says that the flight can not last more than 24 hours. So, if the time of arrival is not more than the time of departure, then this fact takes place.

Now the second question: how do you calculate only the time, with whatever day it stands?

The T-SQL DATEPART function can help here, which will be discussed in the next item.

One thing to keep in mind is that the DATEDIFF function is used. Let’s start with examples. First we will count the number of weeks from Sunday 23-10-2005 to Saturday 29-10-2005. Okay,

SELECT DATEDIFF(wk, '20051023', '20051029');

Common sense suggests that this is a full week, but the above query gives 0. Now let’s take the interval from Saturday 29-10-2005 to Sunday 30-10-2005:

SELECT DATEDIFF(wk, '20051029', '20051030');

The result will be 1, that is, one week. It’s time to give an explanation. The fact is that the DATEDIFF function actually considers a week not the number of days, but the number of transitions from Saturday to Sunday. If you keep that in mind, then an even more amazing example becomes clear:

SELECT DATEDIFF(wk, '20051029 23:59:00', '20051030 00:01:00');

who also gives one!
Is that weird? Maybe, but as they say, whoever is warned is armed. It’s the same for other intervals. For example, the number of days does not give us the number of hours divided by 24 (number of hours per day), but the number of jumps over midnight. You want confirmation? Please .

SELECT DATEDIFF(dd, '20051029 23:59:00', '20051030 00:01:00');

The result is one day, at the same time:

SELECT DATEDIFF(dd, '20051029 00:00:00', '20051029 23:59:59');

gives us zero.

The challenge. Count the number of minutes between two dates – ‘2011-10-07 23:43:00’ and ‘2011-10-08 01:23:00’.

DATEDIFF function in SQL Server

The integrated DATEDIFF function solves the problem:

SELECT DATEDIFF (minute, '2011-10-07T23:43:00', '2011-10-08T01:23:00');

The result is 100 minutes.

Note:
The query uses the standard date representation (ISO) as a text string ‘yyyy-mm-ddThh:mm:ss’, which does not depend on either the local server settings or the server itself.

DATEDIFF function in MySQL

The DATEDIFF function is also present in MySQL, but it has a completely different meaning. DATEDIFF calculates the number of days between two dates that are arguments for this function. In this case, if the date is presented in the date-time format, only the component of the date is used. Therefore, all three requests below will give the same result -1. A positive result will be obtained if the first argument is greater than the second.

SELECT DATEDIFF('2011-10-07T23:43:00', '2011-10-08T01:23:00');
SELECT DATEDIFF('2011-10-07', '2011-10-08');
SELECT DATEDIFF('2011-10-07T23:43:00', '2011-10-08');

We get one day even if the interval between dates is all one second:

SELECT DATEDIFF('2011-10-07T23:59:59', '2011-10-08T00:00:00');

The solution to our problem can be obtained using another built-in function – TIMESTAMPDIFF, which is similar to DATEDIFF function in SQL Server:

SELECT TIMESTAMPDIFF(minute, '2011-10-07T23:43:00', '2011-10-08T01:23:00');

DATEDIFF function in PostgreSQL

PostgreSQL has no function like DATEDIFF (SQL Server) or TIMESTAMPDIFF (MySQL). Therefore, the following sequence of actions can be used to solve the task:

1. to represent the difference between the two dates at an interval;
2. to count the number of seconds in the interval;
3. divide the value obtained in step 2 by 60 to express the result in minutes.

To obtain an interval, you can take the difference between two values of the temporal type, and an explicit type conversion is required:

SELECT timestamp '2011-10-08T01:23:00' - timestamp '2011-10-07T23:43:00';

or as standard

SELECT CAST('2011-10-08T01:23:00' AS timestamp) - CAST('2011-10-07T23:43:00' AS timestamp);

The result is “01:40:00”, which is nothing more than one hour and forty minutes.

You can also use the built-in AGE function, which performs an implicit conversion of the type of your arguments at the same time:

SELECT AGE ('2011-10-08T01:23:00', '2011-10-07T23:43:00');

To get the number of seconds in an interval, use the function:

EXTRACT(EPOCH FROM < interval >)

Let’s present the value of the interval in the last way as the shortest. To get the final solution of the problem we will divide the obtained result by 60:

SELECT EXTRACT(EPOCH FROM AGE('2011-10-08T01:23:00', '2011-10-07T23:43:00'))/60;

In this case, we have the result expressed as an integer since both time values in the problem did not contain seconds. Otherwise, a decimal number will be received, for example: 99.75:

SELECT EXTRACT(EPOCH FROM AGE ('2011-10-08T01:23:00', '2011-10-07T23:43:15'))/60;

DATEDIFF function in Oracle

Oracle also has no functions like DATEDIFF in its arsenal. In addition, Oracle does not support the standard date/time text representation.

We can calculate the interval in minutes, taking into account that the difference in dates (values like date) in Oracle gives the result the number of days (day). Then you just need to multiply the date difference by 24 (number of hours per day) and then by 60 (number of minutes per hour) to calculate the interval in minutes:

SELECT (CAST('2011-10-08 01:23:00' AS date) - CAST('2011-10-07 23:43:00' AS date))*24*60
FROM dual;

Other time intervals can be obtained in the same way.

DatePart, DateAdd and DateDiff functions in SQL Server