13:27

# DATEPART function page 1

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

Datepart Abbreviations
Weekday dw

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;`
and the landing time is

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

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;`

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;`

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

Trip_no Time
1123 2003-04-05 16:20:00.000
1123 2003-04-08 16:20:00.000

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).

Suggested exercises: 43, 75, 110, 119, 143

 Pages 1 2