Add_months function error based on postgres database

Question

I tried to ruh this query in postgres :

Select to_char((select add_months (to_date ('10/10/2019', 'dd/mm/yyyy'), '11/11/2019') ) , 'dd/mm/yyyy') as temp_date

I got an error : Function add_months (date, unknown) does not exist Hint: no function matches the given name and argument types. You might need to add explicit type casts.

Please help

Where in the postgres manual did you find add_months()? But your function call doesn't really make sense to begin with what kind of "months" is '11/11/2019' supposed to be? — user330315
– user330315, Commented Jul 16, 2019 at 11:54
I havent read much about the postgres manual... n yeps i haven't come across the add_months... actually this query is written for oracle n tried to run it on postgres... somebody did it..but i have to do the running 😣 — Stevie Weedy Lyngdoh
– Stevie Weedy Lyngdoh, Commented Jul 17, 2019 at 12:46

score 1 · Accepted Answer · 2019-07-16 13:24:59Z

1

As documented in the manual there is no add_months function in Postgres

But you can simply add an interval:

select to_date('10/10/2019', 'dd/mm/yyyy') + interval '10 months'

If you need to format that date value to something:

select to_char(to_date('10/10/2019', 'dd/mm/yyyy') + interval '10 months', 'yyyy-mm-dd')

edited Jul 16, 2019 at 13:24

answered Jul 16, 2019 at 11:55

user330315

Sign up to request clarification or add additional context in comments.

Comments

Belayer · Accepted Answer · 2019-07-17 20:50:05Z

No one, even running on Oracle, has run the original query- at least not successfully. It appears that query is expecting to add two months together (in this case Oct and Nov). That is not what the function does. It adds an integer number of months to the specified date and returns the resulting date. As indicated in Postgres just adding the desired interval. However, if you have many occurrences ( like converting) of this the following implements a Postgres version.

    create or replace function add_months(
              date_in     date
            , n_months_in integer)
      returns date 
      language sql immutable strict  
      as
    $$
    -- given a date and an integer for number of months return the calendar date for the specified number of months away.
    select (date_in + n_months_in * interval '1 month')::date
    $$ ;

-- test -- +/- 6 months from today. select current_date "today" , add_months(current_date,6) "6 months from now" , add_months(current_date,-6) "6 months ago" ;

Collectives™ on Stack Overflow

Add_months function error based on postgres database

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related