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

sqlpostgresqldate-arithmetic


Solution 1:[1]

Try something like:

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

Solution 2:[2]

Something like this:

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

sql fiddle example

Solution 3:[3]

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

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

Solution 4:[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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Ihor Romanchenko
Solution 2 Roman Pekar
Solution 3 a_horse_with_no_name
Solution 4 Zeeng

Related Questions