Update end date by adding duration to start date in derby database

All we need is an easy explanation of the problem, so here it is.

I created table using Derby database.

CREATE TABLE PROJECT
(PID  INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
PSDATE DATE,
PDURATION INTEGER,
PEDATE DATE,
PRIMARY KEY (PID)
);

I attempted to update end date in this project table, adding duration to start date.

UPDATE PROJECT
SET PEDATE = ADD_MONTHS (PROJECT.PSDATE, PROJECT.PDURATION)
WHERE PID = PROJECT.PID;

But I am getting below error

"[Exception, Error code 30,000, SQLState 42Y03] 'ADD_MONTHS' is not recognized as a function or 
 procedure.
 Line 1, column 1"

Later I tried with below code

select {fn TIMESTAMPADD(SQL_TSI_DAY, +pduration, psdate)}
from name.PROJECT
where pid = project.PID

Can I use TIMESTAMPADD function to update project table in Derby database?
Thanks in advance to help me to solve this problem.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

UPDATE PROJECT 
SET PEDATE = DATE( {fn TIMESTAMPADD(SQL_TSI_MONTH, PDURATION, PSDATE)} );

This query (provided by Akina) updates my end date when adding duration to start date.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply