Русский    English

DATEPART function page 1


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

  1. SELECT DATEPART(hh, time_out)*60 + DATEPART(mi, time_out)
  2. FROM Trip
  3. WHERE trip_no = 1123;
and the landing time is

  1. SELECT DATEPART(hh, time_in)*60 + DATEPART(mi, time_in)
  2. FROM Trip
  3. 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.

  2. WHEN time_dep >= time_arr
  3. THEN time_arr - time_dep + 1440
  4. ELSE time_arr - time_dep
  5. END dur
  6. FROM (SELECT DATEPART(hh, time_out)*60 + DATEPART(mi, time_out) time_dep,
  7. DATEPART(hh, time_in)*60 + DATEPART(mi, time_in) time_arr
  8. FROM Trip
  9. WHERE trip_no = 1123
  10. ) 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.

  1. SELECT DISTINCT pt.trip_no, DATEADD(mi, DATEPART(hh,time_out)*60 +
  2. DATEPART(mi,time_out), date) [time]
  3. FROM Pass_in_trip pt JOIN
  4. Trip t ON pt.trip_no = t.trip_no
  5. 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

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 CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.