Hi ,
Do we have ADD_YEARS() Function in vertica?
or
any alternative to add certain number of years to the given date?
Thanks,
Raj
Do we have ADD_YEARS() Function in vertica?
Moderator: NorbertKrupa
-
- Intermediate
- Posts: 87
- Joined: Wed Apr 25, 2012 8:10 am
Do we have ADD_YEARS() Function in vertica?
Rajasekhar.T|HP ATP Vertica Big Data Solutions V1
Re: Do we have ADD_YEARS() Function in vertica?
Or
Or
Code: Select all
CREATE FUNCTION add_years(dt DATE, inc int) RETURN DATE
AS BEGIN
RETURN ( DATE (YEAR(dt) + inc || '-' || MONTH(dt) || '-' || DAY(dt)) );
END;
Code: Select all
CREATE FUNCTION add_years(dt timestamp, inc int) RETURN timestamp
AS BEGIN
RETURN ((YEAR(dt) + inc || '-' || MONTH(dt) || '-' || DAY(dt) || ' ' || dt::TIME)::TIMESTAMP);
END;
Code: Select all
telecom_db=> select add_years('2021-02-28'::timestamp, 8);
add_years
---------------------
2029-02-28 00:00:00
(1 row)
telecom_db=> select add_years('2021-02-28'::date, -21);
add_years
------------
2000-02-28
(1 row)
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Do we have ADD_YEARS() Function in vertica?
You can also use the ADD_MONTHS() built in function to add years...
Or the TIMESTAMPADD() function...
Code: Select all
dbadmin=> SELECT add_months(DATE '2012-06-06', 12) add_1_year,
dbadmin-> add_months(DATE '2012-06-06', 24) add_2_years,
dbadmin-> add_months(DATE '2012-06-06', 6) add_half_year,
dbadmin-> add_months(DATE '2012-06-06', 30) add_2_and_half_years,
dbadmin-> add_months(DATE '2012-06-06', -12) subtract_1_year;
add_1_year | add_2_years | add_half_year | add_2_and_half_years | subtract_1_year
------------+-------------+---------------+----------------------+-----------------
2013-06-06 | 2014-06-06 | 2012-12-06 | 2014-12-06 | 2011-06-06
(1 row)
Code: Select all
dbadmin=> SELECT timestampadd(year, 1, DATE '2012-06-06') add_1_year,
dbadmin-> timestampadd(year, -1, DATE '2012-06-06') subtract_1_year;
add_1_year | subtract_1_year
---------------------+---------------------
2013-06-06 00:00:00 | 2011-06-06 00:00:00
(1 row)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.