Standard SQL-92 specifies only the functions, which return current system date/time.
Thus, the CURRENT_TIMESTAMP function returns both the date and the time.
In addition, there are functions, which return one of the components.
In view of that restriction, language realizations expand standard by introducing the functions that make handling
datetime data types more convenient for users.
Here we consider datetime functions in T-SQL.
DATEADD
Syntax
DATEADD ( datepart , number, date )
Returns a datetime value based on adding a number of intervals of the datepart
types to the specified date.
For example, we can add to the specified date any number of years, days, hours, minutes etc.
The table from BOL lists the dateparts and abbreviations.
| Datepart |
Abbreviations |
| Year |
yy, yyyy |
| Quarter |
qq, q |
| Month |
mm, m |
| Dayofyear |
dy, y |
| Day |
dd, d |
| Week |
wk, ww |
| Hour |
hh |
| Minute |
mi, n |
| Second |
ss, s |
| Millisecond |
ms |
If we interested in the day, which will be after 01/23/2004, we can write
| SELECT DATEADD(day, 7, current_timestamp) |
or
| SELECT DATEADD(ww, 1, current_timestamp) |
We get the same result; something like 2004-01-30 19:40:58.923.
But we cannot write in that case as follows
| SELECT DATEADD(mm, 1/4, current_timestamp) |
Because of eliminating the fractional part of the argument
datepart, we get 0 instead of one fourth and, as result, the current date.
We can also use the T-SQL
GETDATE() function instead of
CURRENT_TIMESTAMP.
Example (scheme 4). Find the day through a week after the last flight.
| SELECT DATEADD(day, 7, (SELECT MAX(date) max_date FROM pass_in_trip)) |
Usage of subquery as an argument is allowed, for that subquery returns a single value of
datetime type.
DATEDIFF
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 (scheme 4). Find the number of days elapsed between the first and the last flights executed.
| SELECT DATEDIFF(dd, (SELECT MIN(date) FROM pass_in_trip), (SELECT MAX(date) FROM pass_in_trip)) |
Example (scheme 4). 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 fields of
datetime type in the Trip table.
Note, SQL Server up to version 2000 has not temporal data types for storing the date or time separately, which are expected
in the next version (Yukon). 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
| SELECT DATEDIFF(mi, time_out, time_in) dur FROM trip WHERE trip_no=1123, |
(which gives -760) will be incorrect for two reasons.
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?
Now we turn to
DATEPART function.
DATEPART
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 returning by the
DATEPART function in this case (weekday) depends on
the value set by
SET DATEFIRST, which sets the first day of the week. Default value is Sunday = 1
Let us turn to above example. Under suggestions that the departure/landing times are measured with an accuracy of one minute,
we can define it in minutes as minimal integer units. So, departure time of the trip no. 1123 in minutes is
| SELECT DATEPART(hh, time_out)*60 + DATEPART(mi, time_out) FROM trip WHERE trip_no=1123 |
and the landing time is
| 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
second time from the first time; otherwise, 1440 minutes (one day) need to be added to the remainder.
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.
Despite of tedious form, the result is absolute correct in view of above remarks.
Example (scheme 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 of 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.
SELECT 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 run 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.
DATENAME
Syntax
DATENAME ( datepart , date )
This function returns a character string representing the specified datepart of the specified date.
An argument representing the datepart can be one of the values listed in above table.
The function gives us a simple opportunity to concatenate the date components to get any required format.
For example, the query
| SELECT DATENAME ( weekday , '2003-12-31' )+', '+DATENAME ( day , '2003-12-31' )+' '+
DATENAME ( month , '2003-12-31' )+' '+DATENAME ( year , '2003-12-31' ) |
gives the following result
| Wednesday, 31 December 2003 |
Notice that
DATENAME function reveals the difference between
day and
dayofyear values of
datepart argument.
The first gives a character representation of the day of the specified date, whereas the second gives a
character representation of this day from the origin of a year, i.e.
| SELECT DATENAME ( day , '2003-12-31' ) |
gives 31, but
| SELECT DATENAME ( dayofyear , '2003-12-31' ) |
gives 365.
In some cases the
DATEPART function can be replaced by more simple functions. Here they are:
DAY (
date ) - integer representing the day of the specified
date.
This function is equivalent to the
DATEPART(
dd,
date) function.
MONTH (
date ) - integer representing the month of the specified
date.
This function is equivalent to the
DATEPART(
mm,
date) function.
YEAR (
date ) - integer representing the year of the specified
date.
This function is equivalent to the
DATEPART(
yy,
date) function.
@@DATEFIRST function
@@DATEFIRST returns an integer which defines the first day of a week for the current session. In so doing, 1 is corresponding to Monday, but 7 - to Sunday respectively. I.e. if
returns 7, the first day of a week will be Sunday (in according with current site's settings).
Suggested exercises:
78, 110.