Add_months function error based on postgres database

0
votes

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

2
postgresql
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? - a_horse_with_no_name
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

2 Answers

1
votes

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')
0
votes

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" ;