SQL syntax Language 

Transact-SQL Functions handling datetime data types

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

Previous | Index | Next

Home SELECT exercises (rating stages) DML exercises Developers