SQL Query shows no results because dates are stored in year 1900 instead of 2000

I have a query that is supposed to show records from 2022-12-01 to 2022-12-30 but it shows no data. Here’s my SQL string

Select SUM([Quantity]) as "All Quantity" from [InventoryLog] where [Date] BETWEEN '2022-12-01' AND '2022-12-30'

but when I set the years in the date parameters to 1922, there shows data when I execute the query.

Select SUM([Quantity]) as "All Quantity" from [InventoryLog] where [Date] BETWEEN '1922-12-01' AND '1922-12-30'

Am I missing something?

Version: (x86) / LibreOffice Community
Build ID: 184fe81b8c8c30d8b5082578aee2fed2ea847c01
CPU threads: 4; OS: Windows 10.0 Build 19044; UI render: Skia/Raster; VCL: win
Locale: en-US (en_PH); UI: en-US
Calc: CL

Have you in fact looked at the original entry with a four digit year? Are they 2022 or 1922?
If 1922 then possibly a setting incorrect. See → Why entering 1/1/30 into a cell by key press, the result is 01/01/1930 instead of 01/01/2030? - #2 by Ratslinger
At this point I see no reason for the queries to be incorrect.
I see the square brackets are back. That is not for HSQLDB. You should avoid and use quotes.

The years recorded in the database are all 2022 and are shown as 08/12/2022. The entries are inserted by an SQL statement in a macro which is as follows:

strSQLInsert = "INSERT INTO ""InventoryLog"" (""UserID"",""Quantity"",""Operation"",""Date"") VALUES ("&(LoggedInUserID)&","&(quantity)&",'Out','2022-12-08');"

I have also set the interpretation to 2000 to 2029.

I have also tried the Query Wizard that produced the query string below:

SELECT "InventoryLog"."Quantity" AS "Quantity", "InventoryLog"."Date" AS "Date" FROM "InventoryLog" "InventoryLog" WHERE ( "InventoryLog"."Date" > '2022-12-01' AND "InventoryLog"."Date" < '2022-12-30' )

And it still shows nothing.

Also, thank you for your reminders on the square brackets.

Would now think there is something incorrect with your field definition. Is it set as a field type of Date?
It would be of great help if you could post a redacted copy of your Base file.

It is in fact set as of type Date.
I would gladly supply my .odb file. However, it is now more than 4mb – which is too big for upload for this reply.

That is probably due to images. How about, make a copy of the Base file. In the copy delete the images. That should reduce the size.

My file is now under 2mb but the site still prohibits me from uploading the file because “it’s too big”.

I hope this one works.

Nope. You seem to have something else of substantial size in that file. Any unrelated tables you can delete?

can’t be empty

Got it. Will look.


Your post shows SQL with dates of:

BETWEEN '2022-12-01' AND '2022-12-30'

but there are no dates in that table for that range:

Therefore, nothing is returned. No issue except the selection itself. (I modified the first just to insure the field works).

1 Like

Took a short look at some of you Base file/code. I say short because there are many things I would change.
First, as previously mentioned, would NOT use HSQLDB embdded database with images inserted - trouble waiting to happen. Instead use an HSQLDB split database(Apache OpenOffice Community Forum - [Wizard] Create a new 'split' HSQL 2.x database - (View topic)), Firebird file or another server type database. You security for users may as well not exist. There is no protection there to rely on.
No need to register the DB. Seems you use that for SQL. You already have that with the form and being in Base. This creates your statement for use with SQL:

oForm = ThisComponent.Drawpage.Forms.getByName("INTERNAL FORM NAME")
oStatement = oForm.ActiveConnection.createStatement()

It also appears you are using way too much code. I could see dumping a majority of it.

1 Like

but of course! No records were shown because there were no matching results!
Thank you very much for being such a great help!
I am very grateful for your suggestions also. I am learning more and more from you! Cheers!