plus or subtract date

Moderator: NorbertKrupa

Post Reply
Cristobaal
Newbie
Newbie
Posts: 5
Joined: Tue Sep 24, 2013 4:01 pm

plus or subtract date

Post by Cristobaal » Fri Oct 04, 2013 5:00 pm

Hi everybody,

How can subtract or plus a one year with date

For example

plus
getdate + 1 year
2013-10-04 + 1 year = 2014-10-04

substract

getdate + 1 year
2013-10-04 - 1 year = 2012-10-04

Please advices.

Regards!

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: plus or substract date

Post by id10t » Fri Oct 04, 2013 5:30 pm

Hi!

Code: Select all

daniel=> \set dt 'DATE(20131004::VARCHAR)'
daniel=> select :dt;
    DATE    
------------
 2013-10-04
(1 row)

Code: Select all

daniel=> select :dt - INTERVAL '1 YEAR' ;
      ?column?       
---------------------
 2012-10-04 00:00:00
(1 row)

daniel=> select :dt - 1::INTERVAL YEAR;
      ?column?       
---------------------
 2012-10-04 00:00:00
(1 row)
daniel=> select :dt - INTERVAL '1Y' ;
      ?column?       
---------------------
 2012-10-04 00:00:00
(1 row)

daniel=> select :dt - INTERVAL '1YEAR' ;
      ?column?       
---------------------
 2012-10-04 00:00:00
(1 row)

Cristobaal
Newbie
Newbie
Posts: 5
Joined: Tue Sep 24, 2013 4:01 pm

Re: plus or substract date

Post by Cristobaal » Fri Oct 04, 2013 6:18 pm

Hi,

Thank you so much !

Best Regards!

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: plus or substract date

Post by JimKnicely » Fri Oct 04, 2013 7:57 pm

FYI...

We can also use the ADD_MONTHS function...

Code: Select all

dbadmin=> \set dt 'DATE(20131004::VARCHAR)'
dbadmin=> select :dt, add_months(:dt, 12) plus_1_year, add_months(:dt, -12) minus_1_year;
    DATE    | plus_1_year | minus_1_year
------------+-------------+--------------
 2013-10-04 | 2014-10-04  | 2012-10-04
(1 row)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica SQL”