Base Query not reading table with BETWEEN statement

[avvy651]

I have been using a weather readings database for a few years now , and as each month passes I use BETWEEN {d ‘2023-06-01’ } AND {d ‘2023-06-30’ } in sql, for example
Upto the 4th of this month all was working fine after I entered data in the form then saved it. For both the 5th and 6th of June, the data was showing in the table, but not in the query. I found that if I didn’t use the BETWEEN statement, the query read all the table data including 5th and 6th of June, As soon as I used the BETWEEN statement again, the query missed out the aid 2 dates.

Any ideas as to why this has just happened.
Thanks

[Villeroy]

Works for me with embedded HSQL.
PowerFilter.odb (85.5 KB)

See query “Query_June_2023” and the filter form.

I forgot to save the query. It is

select * from "Data" where "D" BETWEEN {d '2023-06-01' } AND {d '2023-06-30' }

[avvy651]

This is my sql for the query:

SELECT "ID1" AS "ID1", "ReadingDate" AS "ReadingDate", "TempMaxC" AS "TempMaxC", "TempWindChillC" AS "TempWindChillC", "TempMinC" AS "TempMinC", "RainFall" AS "RainFall", "Pressure" AS "Pressure", "RH" AS "RH", "WindGust" AS "WindGust", "WindSpeed" AS "WindSpeed", "Direct" AS "Direct", "CloudAmnt" AS "CloudAmnt", "CloudType" AS "CloudType", "Conditions" AS "Conditions", "ReadDate" AS "ReadDate", "TempApparent" AS "TempApparent" FROM "tblWXdataLeeds" WHERE "ReadDate" BETWEEN {d '2023-06-01' } AND {d '2023-06-30' } ORDER BY "ID1" ASC, "ReadDate" ASC

Where or how would I put your line in to give the result

[avvy651]

I tried the said line of sql, but it gave me a syntax error

select * from "tblWXdataLeeds" where "D" BETWEEN {d '2023-06-01' } AND {d '2023-06-30' }

In my sql query if I change the first date to 01/01/2023, then the query only misses out 5th June, and the 6th June is there???

[Villeroy]

After changing some dates to 5th or 6th of June 2023, I can not reproduce anything like that. I can not reproduce your problem.
I don’t even know the software you are using. No operating system, no LibreOffice version and most importantly, I have no idea about the database engine you are using with a Base document. Your WHERE clause simply works with my Base document having an embedded HSQL.

P.S. same non-issue with embedded Firebird. Records with ID 786 and 233 have 5th of June, records 25 and 77 have 6th of June.
PowerFilter.odb (85.4 KB)

[avvy651]

Ok, apologies for not saying what I am using.
Operating System is Linux Mint 21.1
Libre Office version is 7.3.7.2

I’ll just have to use the query without a date filter. It’s weird how it misses 2 dates out but reads subsequent dates.
Thanks

[Villeroy]

And your database software?

[avvy651]

dBase, if that is what you mean

[Villeroy]

The status bar of your Base document indicates that you are using a dBase database? I can not reproduce any such issue with dates in a dBase database.

Same data, same query:
dBase_t92299.zip.odt (11.4 KB)
(remove the .odt suffix from the file name)

[cpb]

@Villeroy
your query

SELECT * FROM "Data" WHERE "D" BETWEEN '2023-06-01' AND '2023-06-30'

returns 11 records when it should return 12.
it does not select the record where ID = 714 and “D” = ‘2023-06-01’.

the SQL below will return all 12 records
BUT
will not work with a column named “D”.

it’s usually best to avoid naming tables or fields “D” or “T” because they may be referenced internally as DATE or TIME, and yes I know we all do it.

so assuming that the date field is called “DTE” this SQL produces an accurate result in dBase:

SELECT * FROM "Data" where "DTE" >= {d '2003-05-01'} and "DTE" <= {d '2003-06-30'}

it looks like dBase does not reliably support the BETWEEN operator for SQL dates.

[Villeroy]

Good catch! SELECT * FROM "Data" WHERE "ID" BETWEEN '10' AND '29' does not include 10 and 29. It returns the rows with IDs from 11 to 28. And the arguments need to have single quotes although the column is numeric.

[RobertG]

BETWEEN should include values of the start and the end. This works right with both internal databases (HSQLDB and Firebird), but it seems it never worked with dBase (tested with LO 5.1.5.2 here also).
It will work right here with

…WHERE "D" >= {d '2023-06-01' } AND "D" <= {d '2023-06-30' }

Don’t know if BETWEEN is a known function in dBase. Might be LO switches this internal.

Have written a bug description for this: Bug 155751

[avvy651]

@cpb
It’s strange that dBase did support the BETWEEN operator for SQL dates for about 4 / 5 years, but now this month it doesn’t !

Thanks anyway.

[Wanderer]

IMHO the communication “above” does not explain why your example is not working. Maybe you can share the dbase-file with reduced data to june or may/june.
.
PS: May I suggest you use “comment” for your replies, as answers are reserved for solutions of your problem on this “ask”-site.

[avvy651]

As requested, with jut May and part of June data
LeedsWXData.odb (22.6 KB)

[avvy651]

I wonder if anyone has tried my reduced data file yet, a I would be interested on the results.
Thanks

[RobertG]

@avvy651 : You reported this all for dBase. Then you send a file, which connects to internal HSQLDB. With dBase the query will exclude the start and the end value. HSQLDB (and Firebird, MySQL …) include this values.
The queries will show the expected results.

[avvy651]

Thanks but it doesn’t as it misses out 2 dates if I use the BETWEEN statement in the SQL query

[Villeroy]

Your table has 41 records and the query has 40 records. The missing record has a date in the 22nd century: 05/06/2102

[avvy651]

@Villeroy
Yes ok, I realised it was one date as I used BETWEEN {d ‘2023-01-01’ } AND {d ‘2023-06-30’ } ORDER BY “ID1” ASC, “ReadDate” ASC.
If I don’t use the BETWEEN statement then all the records are returned, and if I use it for just this month then 2 dates are missing
Is that the case for you