Adding months to a date in PostgreSQL shows syntax error

Question

I am using Postgres 9.0 version. I want to add some months to a date to calculate the new date and update in a table. Here the number of months to be added will be in integer variable. My code is as follows:

declare numberofmonths smallint = 5;
update salereg1 set expdate = current_date + interval cast(numberofmonths as text) month;

The above code shows syntax error at cast. I don't know how to specify the numberofmonths variable as text.. can anyone help me. what is the mistake I did..

Kinetic

Does this: Calculating a date in Postgres by adding months? help? – Athanasia

You should be aware of some peculiarities of our calendar when adding months. What date should be 1 month from 2013-01-28, 2013-01-29, 2013-01-30 or 2013-01-31? Postgres by default will return 2013-02-28 for all 4 dates. Also 12 months from 2012-02-29? 2012 was a leap year, Postgres would return 2013-02-28. – Vetiver

Answer 1

Try something like:

update salereg1 
set expdate = current_date + interval '1 month' * numberofmonths;

Lethbridge

+1 I like this one more than mine. May be I'd write this as '1 month'::interval * numberofmonthsalso. – Neuburger

thanks very helpful specially for those who are new to postgres. – Chillon

Answer 2

Something like this:

update salereg1 set
    expdate = current_date + (numberofmonths::text || ' month')::interval;

sql fiddle example

Neuburger

This is a great trick for the case where the interval value itself to be used is dynamic text +1. – Threadgill

refer to the other answer, you don't have to cast to text it can multiply the interval directly – Pullover

yes if you use interval '1 month' * n. In my answer I was building interval dynamically to get '{n} month'::interval. As said in the comment I like first approach more. – Neuburger

Answer 3

Since Postgres 9.4 you can also use the handy function make_interval:

update salereg1 
  set expdate = current_date + make_interval(months => numberofmonths);

Fino

Answer 4

Understanding it from this analogy between SQL Server and PostgreSQL

SQL Server: -- Get expiration date

SELECT DATEADD(day, valid, purchased) FROM licenses;

PostgreSQL: -- Get expiration date

SELECT purchased + valid * INTERVAL '1 DAY' FROM licenses;

Similarly current_date + number_of_months * INTERVAL '1 MONTH'; so the INTERVAL can be '1 MONTH', '1 DAY', '1 YEAR' like that.

more on this page PostgreSQL - DATEADD - Add Interval to Datetime

Meatus