DATEADD function in HSQLDB

users
#1

I have am using Base to connect to HSQLDB 2.60 so I can use functions like this one. I have this query which calculates my account balances for the last day of the present month.

SELECT "Data"."Account" "Account", SUM( "Data"."Amount" ) "Account Balance", SUM( "Data"."Budget" ) "Budget" FROM "Data", "Transactions" WHERE "Data"."Transactions_ID" = "Transactions"."Transactions_ID" AND "Transactions"."Date" <= LAST_DAY( CURRENT_DATE ) GROUP BY "Data"."Account" HAVING SUM( "Data"."Amount" ) <> 0 ORDER BY "Account" ASC

What I want to do is to calculate these values for the last day of the previous month. I thought using DATEADD to calculate them. Even though I select the sql button (run sql directly), Base does not recognize the function. Specifically, I have tried to put the DATEADD functions within LAST_DAY.

LAST_DAY(DATEADD('mm', 1, CURRENT_DATE)) ought to product the last day of the next month; a -1 should produce the last day of the previous month.

Help will be much appreciated.

Dan