Transact-SQL string and datetime functions

T-SQL Datetime functions

Syntax:

DATEPART(datepart , date)

This function returns an integer representing the specified datepart of the specified date.

The above list of available values of datepart argument is added by the following

Note that the value returned by the DATEPART function in this case (weekday) depends on the value set by SET DATEFIRST parameter, which sets the first day of the week. Default value is Sunday = 1 if language setting is English.

Let us turn back to above example. Under suggestions that the departure/landing times are measured with an accuracy of one minute, we can define duration of flight in minutes as minimal integer units stored. So, departure time of the trip no.1123 in minutes is

Console

Execute

SELECT DATEPART(hh, time_out)*60 + DATEPART(mi, time_out) 
FROM Trip 
WHERE trip_no = 1123;

SELECT DATEPART(hh, time_out)*60 + DATEPART(mi, time_out) FROM Trip WHERE trip_no = 1123;

    Scheme

No scheme

Console

Execute

SELECT DATEPART(hh, time_in)*60 + DATEPART(mi, time_in) 
FROM Trip 
WHERE trip_no = 1123;

SELECT DATEPART(hh, time_in)*60 + DATEPART(mi, time_in) FROM Trip WHERE trip_no = 1123;

    Scheme

No scheme

Now we need to compare whether the landing time exceeds the departure time. If so, we must subtract the second time value from the first time value; otherwise, 1440 minutes (one day = 60*24) need to be added to the remainder.

Console

Execute

SELECT CASE 
 WHEN time_dep >= time_arr 
 THEN time_arr - time_dep + 1440 
 ELSE time_arr - time_dep 
 END dur 
FROM (SELECT DATEPART(hh, time_out)*60 + DATEPART(mi, time_out) time_dep,
 DATEPART(hh, time_in)*60 + DATEPART(mi, time_in) time_arr
 FROM Trip 
 WHERE trip_no = 1123
 ) tm;

SELECT CASE WHEN time_dep >= time_arr THEN time_arr - time_dep + 1440 ELSE time_arr - time_dep END dur FROM (SELECT DATEPART(hh, time_out)*60 + DATEPART(mi, time_out) time_dep, DATEPART(hh, time_in)*60 + DATEPART(mi, time_in) time_arr FROM Trip WHERE trip_no = 1123 ) tm;

    Scheme

No scheme

Here, we use subquery to avoid repetition of cumbersome constructions in the CASE operator. Despite of tedious form, the result is absolute correct in view of above remarks.

Example 7.1.4

Define the departure date and time of the trip no.1123.

Only a date but not a time is stored in the Pass_in_trip table. This is because any trip is executed only once a day. To solve this task, we need to combine the time from the Trip table with the date from the Pass_in_trip table.

Console

Execute

SELECT DISTINCT pt.trip_no, DATEADD(mi, DATEPART(hh,time_out)*60 + 
 DATEPART(mi,time_out), date) [time]
FROM Pass_in_trip pt JOIN 
 Trip t ON pt.trip_no = t.trip_no 
WHERE t.trip_no = 1123;

SELECT DISTINCT pt.trip_no, DATEADD(mi, DATEPART(hh,time_out)*60 + DATEPART(mi,time_out), date) [time] FROM Pass_in_trip pt JOIN Trip t ON pt.trip_no = t.trip_no WHERE t.trip_no = 1123;

    Scheme

No scheme

If you'll run the above query, the following result will be obtained

DISTINCT is used here to eliminate duplicates if any. It should be noted that trip number and day is duplicated in Pass_in_trip table for each passenger of the same trip.

In some cases it is possible to replace DATEPART function with more simple functions. Here they are:

DAY(date) — integer representation of day of the specified date. This function is equivalent to function DATEPART (dd, date).

MONTH(date) — integer representation of month of the specified date. This function is equivalent to function DATEPART (mm, date).

YEAR(date) — integer representation of year of the specified date. This function is equivalent to function DATEPART (yy, date).