Standard SQL92 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 TSQL.
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 20040130 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 TSQL
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 ('19000101').
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_arrtime_dep+1440 ELSE time_arrtime_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 
20030405 16:20:00.000 
1123 
20030408 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 , '20031231' )+', '+DATENAME ( day , '20031231' )+' '+
DATENAME ( month , '20031231' )+' '+DATENAME ( year , '20031231' ) 
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 , '20031231' ) 
gives 31, but
SELECT DATENAME ( dayofyear , '20031231' ) 
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.