CODEWITHSUNDEEP
javavertica
Kid F
Kid F

Reputation: 199

How to add a day in Vertica

In sql world we have "adddate()" function to add certain value on the given day. for instance I can say 

select adddate('2016-2-28',2) as my_day;

and the out put will be '2016-03-01'

In Vertica world we don't have adddate() function. We actually have ADD_MONTHS but not ADD_DAYS.

so my question is how are we going to add a day in Vertica

Upvotes: 3

Views: 13402

Answers (2)

Arthur Putnam
Arthur Putnam

Reputation: 1101

I wanted to add a couple of additional approaches. These are nice if you need to support both timestamps and dates. Additionally, I've seen more support for INTERVAL in other DBMS so if you need cross DBMS support you might want to consider INTERVAL. NOTE: You may need to cast back to date if you need the result to be a date data type.

Using TIMESTAMPADD function

SELECT TIMESTAMPADD('day', 1, '2020-02-28');

(if you need a date returned)

SELECT TIMESTAMPADD('day', 1, '2020-02-28')::date;

Using INTERVAL

SELECT '2016-02-28'::date + INTERVAL '1 day';

(if you need a date returned)

SELECT ('2016-02-28'::date + INTERVAL '1 day')::date;

Upvotes: 2

mauro
mauro

Reputation: 5950

Vertica is clever enough to understand the following:

SQL> select '2016-02-28'::date + 2 ;

Upvotes: 10

Related Questions