Using sql DATEADD function in java

Question

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?

Are you using Derby or HSQLDB? You have included both databases and the answer depends on the actual DBMS you use. — , Aug 27 '12 at 08:08

score 9 · Accepted Answer · 2012-08-27T08:45:23.753

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)})

score 1 · Answer 2 · answered Dec 18 '12 at 19:50

1

ADD OR SUBTRACT ONE DAY IN DERBY select rundate, {fn TIMESTAMPADD(SQL_TSI_DAY, -1, RUNDATE)} from tst.YOUTH

answered Dec 18 '12 at 19:50

Baruch Atta

421
1
5
16

Mohammod Hossain · Answer 3 · 2012-08-28T08:55:00.797

0

For hsqldb:

DATEADD ( , , ) DATEADD ( 'month', 3, DATE '2008-11-22' )

Try it

select DATEADD(Month, -3, '2008-11-22')

For Derby:

Derby supports the JDBC escape function TIMESTAMPADD

TIMESTAMPDIFF( interval, timestampExpression1, timestampExpression2 )

values {fn timestampadd(SQL_TSI_DAY, 1, timestamp('2010-12-31 23:59:59'))};

edited Aug 28 '12 at 08:55

answered Aug 27 '12 at 08:04

Mohammod Hossain

4,134
2
26
37

1

Derby does not have a DateAdd function – Aug 27 '12 at 08:07
But Onca has also included Apache Derby. – Aug 27 '12 at 08:14
This SELECT also fails. So how can I do it in derby? – Onca Aug 27 '12 at 08:16
@Onca: see my answer. And please clarify why you add the HSQLDB libraries if you are using Derby – Aug 27 '12 at 08:19

Using sql DATEADD function in java

3 Answers3

Linked