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? help? — Andrew Morton
– Andrew Morton, Commented Sep 17, 2013 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
– Tometzky, Commented Sep 17, 2013 at 21:16

Ihor Romanchenko · Accepted Answer · 2013-09-17 14:27:53Z

90

Try something like:

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

answered Sep 17, 2013 at 14:27

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

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

2 Comments

roman Over a year ago

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

winnie damayo Over a year ago

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

roman · Accepted Answer · 2013-09-17 14:18:11Z

17

Something like this:

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

sql fiddle example

answered Sep 17, 2013 at 14:18

roman

118k30 gold badges205 silver badges209 bronze badges

3 Comments

Tim Biegeleisen Over a year ago

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

cryanbhu Over a year ago

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

roman Over a year ago

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.

user330315 · Accepted Answer · 2020-07-28 20:43:42Z

7

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, 2020 at 20:43

user330315

Comments

Zeeng · Accepted Answer · 2020-07-28 20:30:28Z

2

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

answered Jul 28, 2020 at 20:30

Zeeng

1,24510 silver badges13 bronze badges

Collectives™ on Stack Overflow

Adding months to a date in PostgreSQL shows syntax error

4 Answers 4

2 Comments

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related