DATEADD

Adds or subtracts an interval to DATE or DATETIME value.

Note

SQream DB does not support the INTERVAL ANSI syntax. Use DATEADD to add or subtract date intervals.

Syntax

DATEADD( interval, number, date_expr )

interval ::=
     YEAR | YYYY | YY
   | QUARTER | QQ | Q
   | MONTH | MM | M
   | DAYOFYEAR | DOY | DY | Y
   | DAY | DD | D
   | WEEK | WK | WW
   | HOUR | HH
   | MINUTE | MI | N
   | SECOND | SS | S
   | MILLISECOND | MS

Arguments

Parameter Description
interval An interval representing a date part. See the table below or the syntax reference above for valid date parts
number An integer expression
date_expr A DATE or DATETIME expression

Valid date parts

Date part Shorthand Definition
YEAR YYYY, YY Year (0 - 9999)
QUARTER QQ, Q Quarter (1-4)
MONTH MM, M Month (1-12)
DAY DD, D, DOY, DAYOFYEAR, DY, Y Days (1-365)
WEEK WK, WW Week of the year (1-52)
HOUR HH Hour (0-23)
MINUTE MI, N Minute (0-59)
SECOND SS, S Seconds (0-59)
MILLISECOND MS Milliseconds (0-999)

Note

  • The first day of the week is Sunday, when used with weekday.

Returns

  • If HOUR, MINUTE, SECOND, or MILLISECOND are added to a DATE, the return type will be DATETIME.
  • For all other date parts, the return type is the same as the argument supplied.

Notes

  • Use negative numbers to subtract from a date
  • Use DATEADD instead of manually figuring out dates. For example, adding a day to February 28th 2020 should result in February 29th 2020. Adding another day should result in March 1st, not February 30th.

Examples

For these examples, consider the following table and contents:

CREATE TABLE cool_dates(name VARCHAR(40), d DATE, dt DATETIME);

INSERT INTO cool_dates VALUES ('Marty McFly goes back to this time','1955-11-05','1955-11-05 01:21:00.000')
    , ('Marty McFly came from this time', '1985-10-26', '1985-10-26 01:22:00.000')
    , ('Vesuvius erupts', '79-08-24', '79-08-24 13:00:00.000')
    , ('1997 begins', '1997-01-01', '1997-01-01')
    , ('1997 ends', '1997-12-31','1997-12-31 23:59:59.999');

Add a quarter to each date

master=> SELECT d as original_date, DATEADD(QUARTER, 1, d) as next_quarter FROM cool_dates;
original_date | next_quarter
--------------+-------------
1955-11-05    | 1956-02-05
1985-10-26    | 1986-01-26
0079-08-24    | 0079-11-24
1997-01-01    | 1997-04-01
1997-12-31    | 1998-03-31

Getting next month’s date

master=> SELECT CURRENT_DATE,DATEADD(MONTH, 1, CURRENT_DATE);
date       | dateadd
-----------+-----------
2019-12-07 | 2020-01-07

Filtering +- 50 years from a specific date

 master=> SELECT name, dt as datetime FROM cool_dates
.          WHERE dt BETWEEN DATEADD(YEAR,-50,'1955-06-01') AND DATEADD(YEAR,50,'1955-06-01');

 name                               | datetime
 -----------------------------------+--------------------
 Marty McFly goes back to this time | 1955-11-05 01:21:00
 Marty McFly came from this time    | 1985-10-26 01:22:00
 1997 begins                        | 1997-01-01 00:00:00
 1997 ends                          | 1997-12-31 23:59:59

Check if a year is a leap year

Returns TRUE if this is a leap year - because adding a day to February 28th is February 29th on a leap year.

-- Should return true for 2020:
master=> SELECT DATEPART(MONTH, DATEADD(DAY,1,'2020-02-28')) = 2 AS "2020 is a leap year";
2020 is a leap year
-------------------
true

-- Should return false for 2021:
master=> SELECT DATEPART(MONTH, DATEADD(DAY,1,'2021-02-28')) = 2 AS "2021 is a leap year";
2021 is a leap year
-------------------
false