Add 1 year to a date

1

In looking at the functions, I didn’t gleam a good way to simply add 1 year to a date. Do I need to extract the parts from the current date add one to the year and reassemble? DATE_ADD only seems to deal in days.

2

It is possible to add an interval to a date like in the example below:

SELECT d + INTERVAL '1' YEAR FROM (VALUES(date '2017-01-13')) tbl(d)
1 Like
3

Thanks worked perfectly. I needed it in the form of:

BETWEEN DATE_TRUNC(‘month’, CURRENT_DATE) AND DATE_TRUNC(‘month’, CURRENT_DATE) + INTERVAL ‘1’ YEAR

And it was fine. Where should I have looked to find this? My mistake was only looking for date functions.

4

Our documentation is lacking a bit for operators. Alternatively, you could have used TIMESTAMPADD function (which is documented) as in:

SELECT TIMESTAMPADD(year, 1, d) FROM (VALUES(date '2017-01-13')) tbl(d)

Anyway, I’ll create an internal ticket to improve the documentation for operators.

2 Likes
5

It seems that the function add_months (not documented) works but when nested in an extract function, throws an error:
SELECT extract(YEAR from Add_Months(CURR_DATE.TODAY,-1))
FROM CURR_DATE

Cannot apply ‘EXTRACT’ to arguments of type ‘EXTRACT( FROM <VARCHAR(65536)>)’. Supported form(s): ‘EXTRACT(<DATETIME_INTERVAL> FROM <DATETIME_INTERVAL>)’ ‘EXTRACT(<DATETIME_INTERVAL> FROM )’

but if I convert to use the timestampadd function, that works. Why won’t above syntax work?
SELECT extract(YEAR From TIMESTAMPADD(MONTH,-1,CURR_DATE.TODAY)) as x
FROM CURR_DATE

6

Hello @andre

Can you try this:

SELECT extract(YEAR from cast(Add_Months(CURR_DATE.TODAY,-1) as date)FROM CURR_DATE;

Thanks,
@Rakesh_Malugu

7

Thanks. That worked after adding one missing parenthesis in your response. Sadly, the SQL we had isn’t simply drop into Dremio.