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" )
?
Wow, this looks very complex. I’ll start to work my way through your queries
reply to CRDF
your query always replies as 2 days, no matter the input to :sd and :ed
sd = 2026-01-05
ed = 2026-02-08
Ah. I am using his updated file “days_in_months_embedded.odb"
Can you re-post the contents of TBL from his previous reply?
Thx CRDF. I can replicate your results now.
I’m still trying to wrap my head around the solution. I thought D1 and D2 represented the start and end dates, so why introduce sd and ed?
TBL is being used as a Calendar, so i would need 365 rows for each day of the year. And column D2 is not needed in this scenario.
use of a genuine calendar table makes this pretty straight forward.
.
using Villeroys uploaded “dates.odb”:
I am using the table “TBL”.
I added the table “tCalendar”, its date range is 2025-12-31 to 2030-12-31.
.
I add 2 queries, “q1.sql” and “qf_1”.
“qf_1” is identical to “q1.sql” but is saved in parser mode.
.
I added the form “fForm1”, it’s a copy of Villeroys form “Form” (saved me time).
data source for “fForm1” is the query “qf_1”.
.
I added 1 record to the table “TBL” for testing purposes, ID = 53, and date range = 2025-12-31 to 2027-12-12.
seems to work ok.
Dates_2.odb (54.9 KB)
I replaced that faulty solution with days_in_month_embedded.odb
Same form, 4 queries instead of 3, correct result table, based on 12 months and a list of year numbers.
Indeed, using a list of indexed dates for the next decade is far more efficient and simple. I was somewhat deadlocked.
≠ solutions. A Calendar Table has just 1 column.
This is indeed a legitimate resource, common in “Data Analytics”.
IMHO, the ultimate solution with embedded HSQL, a decade of indexed dates and a simple query:
days_in_months.odb (127.5 KB)
Thank everyone for there suggestions/solutions.
I will use the calendar table in my solution.
