Using sql DATEADD function in java

sql derby date-arithmetic dateadd
Onca picture Onca · Aug 27, 2012 · Viewed 8.2k times · Source

When I run queries using DATEADD it seems that the database does not recognize this function. also when I just run select DATEADD(Month, -3, GETDATE()) I'm getting:

Error code -1, SQL state 42X01: Syntax error: Encountered "<EOF>" at line 1, column 36.

I added the JAR file from hsqldb-2.2.9 as you can see

enter image description here

What am I missing here?

Answer

a_horse_with_no_name picture a_horse_with_no_name · Aug 27, 2012

Derby does not have a DATEADD function. You need to use the JDBC function timestampadd to achieve this:

select {fn TIMESTAMPADD(SQL_TSI_MONTH, -3, CURRENT_TIMESTAMP)}
from sysibm.sysdummy1

An alternative to using sysdummy1 is the ANSI standard values clause which works both in Derby and HSQLDB:

values ({fn TIMESTAMPADD(SQL_TSI_MONTH, -3, CURRENT_TIMESTAMP)})

Related questions

String to java.sql.Date

I realize this has been asked a lot. I did actually look. I've spent hours looking around and trying to figure this out. I'm supposed to be making a program that stores what amounts to a list of appointments in …

Read More
Alter a table column with auto increment by 1 in derby

I have created a table in derby Netbeans and now i realize that i need to make a column as auto incremented by 1 which is a primary key. How can i do so? I tried the following code but was …

Read More
How to alter a column datatype for derby database?

I am trying to alter a datatype for a derby db column. The current price column is set as DECIMAL(5,0). I would like to alter it to DECIMAL(7,2). I did this : alter table item alter column price set data type …

Read More