Categories: Date/Time

DATE_ADD

Add (or subract) days from a date/timestamp value or column

Syntax

DATE_ADD(givenDate string formatted as date, nrDays integer) → DATE

  • givenDate: String-formatted date, of the form ‘YYYY-MM-DD’
  • nrDays: Integer number of days to be added (or subtracted) from the givenDate. To subtract days, pass a negative number.

Examples

SELECT date_add('2021-04-01', 2)
-- 2021-04-03
SELECT date_add('2021-04-01', 2)
-- 2021-04-03

DATE_ADD(givenDate date, nrDays integer) → DATE

  • givenDate: Date value to which to add days (either a database column in DATE format, or literal value explicitly converted to DATE)
  • nrDays: Integer number of days to be added (or subtracted) from the givenDate. To subtract days, pass a negative number.

Examples

SELECT date_add(to_date('2021-04-01'), 2)
-- 2021-04-03

DATE_ADD(givenTimestamp string formatted as date/timestamp, nrDays float) → TIMESTAMP

  • givenTimestamp: String-formatted timestamp, of the form ‘YYYY-MM-DD HH24:MI:SS’
  • nrDays: Floating point (fractional) number of days to be added (or subtracted) from the givenDate. To subtract days, pass a negative number.

Examples

SELECT date_add('2021-04-01 01:00:00.000', 2.5)
-- 2021-04-03 13:00:00

DATE_ADD(givenTimestamp timestamp, nrDays float) → TIMESTAMP

  • givenTimestamp: Date value to which to add days (either a database column in DATE format, or literal value explicitly converted to DATE)
  • nrDays: Floating point (fractional) number of days to be added (or subtracted) from the givenDate. To subtract days, pass a negative number.

Examples

SELECT date_add(to_timestamp('2021-04-01 01:00:00.000'), 2.5)
-- 2021-04-03 13:00:00

DATE_ADD(givenDate string formatted as date/timestamp, timeInterval string formatted as Interval) → DATE

  • givenDate: String-formatted date, of the form ‘YYYY-MM-DD’
  • timeInterval: Single-quoted string representing a time interval. Expressed as a number, followed by one of the following strings - ‘year’, ‘quarter’, ‘month’, ‘week’, ‘day’, ‘hour’, ‘minute’, ‘second’

Examples

SELECT date_add('2021-04-01', '2 days')
-- 2021-04-03

DATE_ADD(givenDate date, timeInterval string formatted as Interval) → DATE

  • givenDate: String-formatted date, of the form ‘YYYY-MM-DD’
  • timeInterval: Single-quoted string representing a time interval. Expressed as a number, followed by one of the following strings - ‘year’, ‘quarter’, ‘month’, ‘week’, ‘day’, ‘hour’, ‘minute’, ‘second’

Examples

SELECT date_add(to_date('2021-04-01'), '2 days')
-- 2021-04-03

DATE_ADD(givenTimestamp string formatted as date/timestamp, timeInterval string formatted as Interval) → TIMESTAMP

  • givenTimestamp: String-formatted timestamp, of the form ‘YYYY-MM-DD HH24:MI:SS’
  • timeInterval: Single-quoted string representing a time interval. Expressed as a number, followed by one of the following strings - ‘year’, ‘quarter’, ‘month’, ‘week’, ‘day’, ‘hour’, ‘minute’, ‘second’

Examples

SELECT date_add('2021-04-01 00:00:00', '2 days')
-- 2021-04-03 00:00:00

DATE_ADD(givenTimetamp timestamp, timeInterval string formatted as Interval) → TIMESTAMP

  • givenTimetamp: String-formatted date, of the form ‘YYYY-MM-DD’
  • timeInterval: String-formatted date, of the form ‘YYYY-MM-DD’

Examples

SELECT date_add(to_timestamp('2021-04-01 00:00:00', '2 days')
-- 2021-04-03 00:00:00

DATE_ADD(givenTime string formatted as time, timeInterval string formatted as Interval) → TIME

  • givenTime: String-formatted time, of the form ‘HH24:MI:SS’
  • timeInterval: Single-quoted string representing a time interval. Expressed as a number, followed by one of the following strings - ‘year’, ‘quarter’, ‘month’, ‘week’, ‘day’, ‘hour’, ‘minute’, ‘second’

Examples

SELECT date_add('00:00:00', '2 minutes')
-- 00:02:00

DATE_ADD(givenTime time, timeInterval string formatted as Interval) → TIME

  • givenTime: String-formatted date, of the form ‘YYYY-MM-DD’
  • timeInterval: Single-quoted string representing a time interval. Expressed as a number, followed by one of the following strings - ‘year’, ‘quarter’, ‘month’, ‘week’, ‘day’, ‘hour’, ‘minute’, ‘second’

Examples

SELECT date_add(to_time('00:00:00'), '2 minutes')
-- 00:02:00