DATEPART function page 1
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
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.
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.
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.
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).