loading..
Русский    English
20:17
go

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
  1. SELECT DATEADD(day, 7, current_timestamp);
or

Console
Execute
  1. 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
  1. 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
  1. SELECT DATEADD(day, 7, (SELECT MAX(date) max_date 
  2.  FROM pass_in_trip
  3.  )
  4.  );

Usage of subquery as an argument is allowed, for that subquery returns a single value of datetime type. 

Suggested exercises: 94
Bookmark and Share
Pages 1 2
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-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.