How to implement add_months in PostgreSQL ?? Like oracle ADD_MONTHS returns the date. Ex. ADD_MONTHS(hire_date,1)
3 Answers
use
hire_date + interval '1 month'
this will return exactly one month added to hire_date.
For More References on date and time functions in postgre Date time function
1 Comment
maksymsan
This doesn't cover the case when
hire_date is the last day of a month. add_months(to_date('2025-02-28','yyyy-mm-dd'), 1) returns 2025-03-31, while the month interval logic in PostgreSQL returns 2025-03-28.CREATE FUNCTION add_months(start DATE, months INT) RETURNS DATE AS
$$
SELECT (start + months * '1 month'::INTERVAL)::DATE
$$
LANGUAGE sql IMMUTABLE
Comments
In PostgreSQL you can create a function to do the job
create or replace function ADD_MONTHS(var_dte date,cnt int) returns setof date as
$$
declare
qry text;
begin
qry = format( 'select (''%s''::date + interval ''%s'')::date',var_dte,cnt||' month') ;
RETURN QUERY
EXECUTE qry;
end
$$
language plpgsql
and call this function
select ADD_MONTHS('2015-11-27',1)
Result:
add_months
date
----------
2015-12-27
in your case
select hire_date
,ADD_MONTHS(hire_date,1)
from table_name
hire_date + interval '1' monthADD_MONTHShas a special logic when the first argument is the last day of a month. In PostgreSQL,'2025-02-28'::date + interval '1 month'returns2025-03-28. While in Oracleadd_months(to_date('2025-02-28','yyyy-mm-dd'), 1)returns2025-03-31. So, the already answered question does not answer how to implementadd_monthslogic properly in PostgreSQL.