I am using embedded HSQLDB.
I would like to count the days in each month from a start and end date.
ex.
startdate = 20260301
end date = 20260415
Desired answer
March = 31
April = 15
Any suggestions greatly appreciated.
Searching other topics it looks like a need a macro for this.
days_in_months_embedded.odb (13.7 KB)
Sorry, I had to replace the file with a better version. My first approach was faulty.
Embedded HSQL 1.8 has an insufficient portfolio of date functions.
This attachment with embedded HSQL calculates integer day numbers for all dates involved.
Finally, the days are calculated by subtracting these day numbers.
For instance, DateDiff('day', '1899-12-30', "some_date") calculates the same day number that is used as a pseudo-date value in Calc and Writer. If you format these integers as dates, Base shows the same date as “some_date” in the expression.
Another confusing problem with this kind of calculation is this:
DateDiff('day', '2026-01-01', '2026-01-31') returns 30 instead of 31 because it gets the time span between '2026-01-01 00:00:00' and 2026-01-31 00:00:00, which does NOT include the last day.
Therefore I calculate the first day of next month, which includes the last day of month, and I add one day to the end date of the given date range.
All the datediff calculation makes this thing very slow as you notice when you navigate the form. The subform takes seconds to show up.
I tested the same thing with HSQL2 which requires less datediff, making the queries way faster. HSQL has a function LAST_DAY returning the last day of a month.
What about
SELECT MONTHNAME( "D1" ) "MES", COUNT( * ) "DAYS" FROM "TBL" WHERE "D1" BETWEEN :sd AND :ed GROUP BY MONTH( "D1" )
?