Skip to main content

dateadd

1. DATEADD Function​

The DATEADD function in H2 database is used to add or subtract a specified interval of time to a given date or time.

2. Syntax​

The syntax for the DATEADD function in H2 database is as follows:

DATEADD(interval, number, date)

Arguments​

  • interval: The interval of time to add or subtract. It should be a string value representing the interval type. Valid interval types are: 'YEAR', 'MONTH', 'DAY', 'HOUR', 'MINUTE', 'SECOND', 'MILLISECOND'.
  • number: The number of intervals to add or subtract. It should be an integer value.
  • date: The date or time to which the interval needs to be added or subtracted. It should be a valid date or time value.

Return​

  • The DATEADD function returns a new date or time value after adding or subtracting the specified interval.

3. Notes​

  • The DATEADD function in H2 database supports a variety of interval types such as years, months, days, hours, minutes, seconds, and milliseconds. These interval types are case-insensitive.
  • The number argument can be positive to add intervals or negative to subtract intervals.
  • The date argument can be a valid date or time value, including literals or column names from a table.
  • If the date argument is not a valid date or time value, the DATEADD function will return NULL.
  • Pay attention to the interval types and ensure they are spelled correctly to avoid errors.

4. Examples​

Here are a few examples demonstrating the usage of the DATEADD function in H2 database:

Example 1 - Adding 3 days to a specific date:​

SELECT DATEADD('DAY', 3, DATE '2022-01-01') AS new_date;

Output:

new_date
----------
2022-01-04

Example 2 - Subtracting 1 hour from a specific time:​

SELECT DATEADD('HOUR', -1, TIME '12:30:00') AS new_time;

Output:

new_time
----------
11:30:00

Example 3 - Adding 2 months to a date stored in a column:​

CREATE TABLE dates (
  id INT PRIMARY KEY,
  event_date DATE
);

INSERT INTO dates VALUES (1, DATE '2022-03-15'), (2, DATE '2022-06-30');

SELECT id, event_date, DATEADD('MONTH', 2, event_date) AS new_date
FROM dates;

Output:

id | event_date  |  new_date
---+-------------+------------
1  | 2022-03-15  | 2022-05-15
2  | 2022-06-30  | 2022-08-30
  • datediff - Calculate the difference between two dates or times.
  • DATEPART - Extract a specific part (year, month, day, etc.) from a date or time.