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

[hadjiamit]

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: 7.3.5.2 (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

[Ratslinger]

Hello,
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.

[hadjiamit]

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.

[Ratslinger]

@hadjiamit
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.

[hadjiamit]

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.

[Ratslinger]

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.

[hadjiamit]

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

[hadjiamit]

file
I hope this one works.

[Ratslinger]

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

[hadjiamit]

can’t be empty

[Ratslinger]

Got it. Will look.

[Ratslinger]

@hadjiamit

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).

[Ratslinger]

@hadjiamit
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.

[hadjiamit]

facepalms
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!