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..

Does this: [Calculating a date in Postgres by adding months?](http://stackoverflow.com/questions/5909363/calculating-a-date-in-postgres-by-adding-months) help? — Andrew Morton, Sep 17 '13 at 14:04
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. — Tometzky, Sep 17 '13 at 21:16

score 86 · Answer 1 · answered Sep 17 '13 at 14:27

86

Try something like:

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

answered Sep 17 '13 at 14:27

Ihor Romanchenko

26,995
8
48
44

+1 I like this one more than mine. May be I'd write this as `'1 month'::interval * numberofmonths`also. – Roman Pekar Sep 17 '13 at 14:46
thanks very helpful specially for those who are new to postgres. – winnie damayo Jan 03 '18 at 04:16

score 16 · Answer 2 · answered Sep 17 '13 at 14:18

16

Something like this:

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

sql fiddle example

answered Sep 17 '13 at 14:18

Roman Pekar

107,110
28
195
197

This is a great trick for the case where the interval value itself to be used is dynamic text +1. – Tim Biegeleisen Jun 05 '19 at 07:40

score 5 · Answer 3 · answered Jul 28 '20 at 20:43

5

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

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

answered Jul 28 '20 at 20:43

score 2 · Answer 4 · answered Jul 28 '20 at 20:30

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

Adding months to a date in PostgreSQL shows syntax error

4 Answers4

Linked