Base query - need to return data for calender month!

OK, this is probably an FAQ but…

I need to run a query that extracts data for the previous calender month from a Libreoffice DB. Something like a ‘SELECT * from Table BETWEEN startofmonth and endofMonth’ …type…thing.

Simple. Except I cannot find the answer. I’ve been hunting all morning and nothing works in LO!

I need this to at least partly automate invoicing for my clients. I cannot keep up with everything by manual typing anymore!

It’s been many years since I have used SQL at all and it was returning results by CLI for test purposes while this has to go to a formatted report. I am not in tech any more so need a little nudge here! My clients are repeat every month so I do not need pretty one off invoices. I need “Who came when - report - send.”

Thanks in advance. This is all I need to halve my paperwork load.
[Oh, running LO 6.0 under Linux KDE Neon if that helps. I guess I can PPA if it will help.]

You don’t precise which database you are running. If it is the default embedded HSQLDB 1.8, then you can find a user guide on Hsqldb User Guide

§9 on that page gives a detailed description of the supported SQL syntax. I suggest next you to follow the link to “Built-in Functions and Stored Procedures” which lists somewhere all the builtin functions for date/time handling.

I presume that something like

SELECT * FROM “Table” WHERE MONTH(“DateField”) = 8

should do what you request.

If you use another database system, know then that each of them has its own SQL dialect and its own set of such builtin functions.


OK. I did not know differing HSQLDB renderings or even what LO was using. I am using all defaults since I did even know there were alternatives at this point!
Sorry, it has been soooo long. I was querying Oracle back in the day in a total Microsoft house and I never thought I’d be tackling DB’s again!
I’m not well today so I will rest now, get better, check this out and get back.
Many thanks.
Oh, and yes, I do know about the hundreds of SQL dialects which is partly why everything I have tried to date has not worked!