DuckDB provides us with an easy way to add one or more time intervals to date values. This includes adding one or more years to a date.

Here are two options for adding one or more years to a date in DuckDB.

Option 1: The + Operator

Probably the easiest way to add years to a date in DuckDB is with the + operator. This can be used in the same manner that we might use it for when adding two numbers.

Example:

SELECT DATE '2050-01-01' + INTERVAL 3 YEAR;

Result:

+------------------------------------------------------------------------------------+
| (CAST('2050-01-01' AS DATE) + to_years(CAST(trunc(CAST(3 AS DOUBLE)) AS INTEGER))) |
+------------------------------------------------------------------------------------+
| 2053-01-01 00:00:00                                                                |
+------------------------------------------------------------------------------------+

We can also use the plural form YEARS instead of YEAR:

SELECT DATE '2050-01-01' + INTERVAL 3 YEARS;

Result:

+------------------------------------------------------------------------------------+
| (CAST('2050-01-01' AS DATE) + to_years(CAST(trunc(CAST(3 AS DOUBLE)) AS INTEGER))) |
+------------------------------------------------------------------------------------+
| 2053-01-01 00:00:00                                                                |
+------------------------------------------------------------------------------------+

It’s also possible to specify the interval in days or months. Additionally, days can be represented by an integer (without needing to specify INTERVAL). But we need to keep in mind that not all years have the same number of days (due to leap years). So specifying 365 days would work sometimes, but not all the time. Here’s an example of what I mean:

SELECT 
    DATE '2048-01-01' + 365 AS Days,
    DATE '2048-01-01' + INTERVAL 12 MONTHS AS Months,
    DATE '2048-01-01' + INTERVAL 1 YEAR AS Years;

Result:

+------------+---------------------+---------------------+
|    Days    |       Months        |        Years        |
+------------+---------------------+---------------------+
| 2048-12-31 | 2049-01-01 00:00:00 | 2049-01-01 00:00:00 |
+------------+---------------------+---------------------+

In this case, specifying 12 months had the same effect as specifying 1 year. But specifying 365 days didn’t work, due to the year 2048 being a leap year (February has 29 days that year). In this case, we should’ve specified 366 days. Using YEARS or even MONTHS relieves us from having to know exactly how many days to add.

One thing to note is that when we pass an integer like this, the result is returned as a DATE value, instead of a TIMESTAMP value. That said, we could always use the cast() function to cast the resulting TIMESTAMP value to a DATE if required (and vice-versa).

Speaking of TIMESTAMPs, we can also add to a TIMESTAMP value if needed:

SELECT TIMESTAMP '2050-01-01 12:35:40' + INTERVAL 3 YEARS;

Result:

+--------------------------------------------------------------------------------------------------+
| (CAST('2050-01-01 12:35:40' AS TIMESTAMP) + to_years(CAST(trunc(CAST(3 AS DOUBLE)) AS INTEGER))) |
+--------------------------------------------------------------------------------------------------+
| 2053-01-01 12:35:40                                                                              |
+--------------------------------------------------------------------------------------------------+

We can also add more than just years:

SELECT DATE '2050-01-01' + INTERVAL '10 YEARS 3 DAYS 2 HOURS 5 MINUTES 17 SECONDS';

Result:

+-------------------------------------------------------------------------------------------------+
| (CAST('2050-01-01' AS DATE) + CAST('10 YEARS 3 DAYS 2 HOURS 5 MINUTES 17 SECONDS' AS INTERVAL)) |
+-------------------------------------------------------------------------------------------------+
| 2060-01-04 02:05:17                                                                             |
+-------------------------------------------------------------------------------------------------+

When we do this, we need to pass the interval as a string (i.e. enclosed in single quotes).

Option 2: The DATE_ADD() Function

Another option for adding years to a date is the date_add() function:

SELECT date_add( DATE '2050-01-01', INTERVAL 3 YEAR);

Result:

+-------------------------------------------------------------------------------------------+
| date_add(CAST('2050-01-01' AS DATE), to_years(CAST(trunc(CAST(3 AS DOUBLE)) AS INTEGER))) |
+-------------------------------------------------------------------------------------------+
| 2053-01-01 00:00:00                                                                       |
+-------------------------------------------------------------------------------------------+

As with the + operator, we can use YEARS instead of YEAR:

SELECT 
    date_add( DATE '2050-01-01', INTERVAL 3 YEAR) AS YEAR,
    date_add( DATE '2050-01-01', INTERVAL 3 YEARS) AS YEARS;

Result:

+---------------------+---------------------+
|        YEAR         |        YEARS        |
+---------------------+---------------------+
| 2053-01-01 00:00:00 | 2053-01-01 00:00:00 |
+---------------------+---------------------+

We can also add more than just years, just like with the + operator:

SELECT date_add( DATE '2050-01-01', INTERVAL '10 YEARS 3 DAYS 2 HOURS 5 MINUTES 17 SECONDS');

Result:

+--------------------------------------------------------------------------------------------------------+
| date_add(CAST('2050-01-01' AS DATE), CAST('10 YEARS 3 DAYS 2 HOURS 5 MINUTES 17 SECONDS' AS INTERVAL)) |
+--------------------------------------------------------------------------------------------------------+
| 2060-01-04 02:05:17                                                                                    |
+--------------------------------------------------------------------------------------------------------+