DATEADD function

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 BOL (SQL Server — Books On Line) lists the dateparts and abbreviations.

If you wish to know, which day will be after a week from today, you can write:

SELECT DATEADD(day, 7, current_timestamp);

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

SELECT DATEADD(mm, 1/4, current_timestamp);

We can also use the T-SQL GETDATE() function instead of CURRENT_TIMESTAMP.

Example 7.1.1

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

Taking the task 7.1.1 as example, let’s consider addition of time interval to a date for some other DBMS

MySQL

MySQL has similar function with unsimilar parameters. Here is the syntax:

DATE_ADD(date, INTERVAL value addunit)

Where

date - the date which an interval is added to;

value - the interval value;

addunit - the interval type.

There are available the following interval types with selfdescribed names:

The solution of our task for MySQL takes the form:

SELECT DATE_ADD((SELECT MAX(date) FROM pass_in_trip),
             interval 7 day) next_wd;

To add the interval consisting of few time components, you can use the substring of standard representation of date/time. So, let’s add 1 day and 3 hours to ‘2018-01-27T13:00:00’:

SELECT DATE_ADD('2018-01-27T13:00:00' , interval '1T3'  DAY_HOUR);

Addition of 1 day and 15 seconds to the same date will be the following:

SELECT DATE_ADD('2018-01-27T13:00:00', interval '01T00:00:15'  DAY_SECOND);

PostgreSQL and Oracle

These DBMS do not use any function for addition of intervals. In this case ordinary operator “+” is used:

SELECT MAX("date") + interval '7' day next_wd
FROM pass_in_trip;

Take into account character data type for interval value.

Addition of 1 day and 3 hours

PostgreSQL

PostgreSQL has not composite intervals, so we should either express interval value in the terms of lesser interval

SELECT timestamp'2018-01-27T13:00:00' + interval '27'  hour;

or add two intervals

SELECT timestamp'2018-01-27T13:00:00' + interval '3' hour + interval '1' day;

In the same manner it could be done for addition of one day and 15 seconds, for example:

SELECT timestamp'2018-01-27T13:00:00' + interval '15' second + interval '1' day;

Oracle

Oracle uses composite intervals, for example (one day and 3 hours):

SELECT timestamp'2018-01-27 13:00:00' + interval '01 03'  DAY to HOUR from dual;

or (1 day and 15 seconds):

SELECT timestamp'2018-01-27 13:00:00' + interval '01 00:00:15'  DAY to SECOND from dual;

Naturally, we could add two simple intervals as in the case of PostgreSQL.