Русский    English

DATEDIFF function page 1


  1. DATEDIFF(datepart, startdate, enddate)

The function returns the interval of date and time elapsed between two specified dates - startdate and enddate. This interval may be meassured in different units. Available values of the argument datepart listed above for the DATEADD function.

Example 7.1.2

Find the number of days elapsed between the first and the last flights executed.

  2. FROM pass_in_trip
  3. ),
  4. (SELECT MAX(date)
  5. FROM pass_in_trip
  6. )
  7. );

Example 7.1.3

Find the duration in minutes of the flight for the trip no. 1123.

It should be noted that the departure time (time_out) and the landing time (time_in) are stored in the columns of datetime type in the Trip table. Note,  A database management system (DBMS) by Microsoft Corporation. SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL Server up to version 2005 had not temporal data types for storing the date or time separately. Because of this, when inserting only the time in the datetime column (for example, UPDATE trip SET time_out = '17:24:00' WHERE trip_no=1123), the time will be supplemented by the default date value ('1900-01-01').

The simple solution

  1. SELECT DATEDIFF(mi, time_out, time_in) dur
  2. FROM trip
  3. WHERE trip_no = 1123;
(which gives -760) will be incorrect for two reasons.

Firstly, the value obtained in such manner will be incorrect for the trips that depart in one day and land in another one. Secondly, it is unreliably to make any suggestions on a day that is only presented of necessity to correspond to datetime data type.How can we know that a plane lands in the next day? This knowledge comes from the subject area, which says the flight cannot be over 24 hours. So, if the landing time not more than the departure time, this is the case.

The second question: how do we calculate only the time apart from the day?

The   T-SQL (Transact-SQL) is Microsoft`s and Sybase`s proprietary procedural extension to SQL.T-SQL DATEPART function could help here; we'll talk about it in the following chapter.

There is one feature of implementation of DATEDIFF function you should take into account. We'll begin with examples. First we'll count number of weeks since Sunday 10-23-2005 till Saturday 10-29-2005. So,

  1. SELECT DATEDIFF (wk, '20051023 00:00:00', '20051029 23:59:59');

The common sense tells us that this is a full week, however the above query gives 0. Now we shall take an interval since Saturday 29-10-2005 till Sunday 30-10-2005:

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

As a result we shall receive 1, i.e. one week. It is a time to give an explanation. The answer is in the DATEDIFF functionality that actually considers week as not a number of days, but as a number of transitions since Saturday on Sunday. If one keeps in mind this fact, then even the more surprising example will be clear :

  1. SELECT DATEDIFF(wk, '20051029 23:59:00', '20051030 00:01:00');
which too gives unity!

Is it strange? Probably, but, as they say, who is notified - is armed. The same takes place and for other intervals. For example, the quantity of days gives us not a number of hours, divided by 24 (quantity of hours per day), but a number of transitions over midnight. Do you want acknowledgement? Please

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

As a result we receive one day. At the same time

  1. SELECT DATEDIFF(dd, '20051029 00:00:00', '20051029 23:59:59');
gives us 0.

If you want, you can continue experiments with other time intervals. Use our Console for that.

Suggested exercises: 76, 93

Bookmark and Share
Pages 1 2
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100