Transact-SQL string and datetime functions

T-SQL Datetime functions

Syntax:

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.

Console

Execute

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

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

    Scheme

No scheme

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

Console

Execute

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

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

    Scheme

No scheme

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,

Console

Execute

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

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

    Scheme

No scheme

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:

Console

Execute

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

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

    Scheme

No scheme

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 :

Console

Execute

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

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

    Scheme

No scheme

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

Console

Execute

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

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

    Scheme

No scheme

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

Console

Execute

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

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

    Scheme

No scheme

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