CODEWITHSUNDEEP
sqlpostgresqldate-arithmetic
Haji
Haji

Reputation: 2077

Adding months to a date in PostgreSQL shows syntax error

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

Upvotes: 39

Views: 90974

Answers (4)

user330315
user330315

Reputation:

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

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

Upvotes: 6

Zeeng
Zeeng

Reputation: 1245

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

Upvotes: 2

Ihor Romanchenko
Ihor Romanchenko

Reputation: 28541

Try something like:

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

Upvotes: 89

roman
roman

Reputation: 117380

Something like this:

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

sql fiddle example

Upvotes: 16

Related Questions