15:58

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

Transact-SQL string and datetime functions

T-SQL Datetime functions

DATEADD function page 1

The function DATEADD ( datepart , number, date ) returns a datetime data type value based on adding a number of intervals (integer) of the datepart type to the specified date.

For example, we can add to the specified date any number of years, days, hours, minutes etc. The table from Электронная документация SQL Server Books OnlineBOL (SQL Server — Books On Line) 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 you wish to know, which day will be after a week from today, you can write:

Console

Execute

SELECT DATEADD(day, 7, current_timestamp);

Console

Execute

SELECT DATEADD(ww, 1, current_timestamp);

We'll get the same result within the elapse time between running the queries.

But we cannot write in that case as follows

Console

Execute

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 (Transact-SQL) is Microsoft`s and Sybase`s proprietary procedural extension to SQL.T-SQL GETDATE() function instead of CURRENT_TIMESTAMP.

Example 7.1.1

Find the day through a week after the last flight.

Notes:

In examples of the given chapter the database "Aeroflot" is used. The description of this scheme (and all other schemes which are used on the site for the solving of exercises) you can find in the Appendix 1.

Console

Execute

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.

Suggested exercises: 94

Pages

DATEADD date_time functions

Previous [T-SQL Datetime functions]

[DATEDIFF function] Next

Last added:

Tags

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 Problems and solutions

S. I. Moiseenko

Transact-SQL string and datetime functions

T-SQL Datetime functions

DATEADD function page 1