Another query issue

[jodybingo]

I have a query where I enter a starting date, then an ending date. All my date formats are in the mm/dd/yyyy format but when i run my query the dialog box demands a mm/dd/yyyy format. (the language settings for libreoffice are also in the d/m/y format).
How can I set the dialog to accept dd/mm/yyyy?

Edit 2019-06-06 (by @Ratslinger) copied from not an answer answer:

Sorry bout the missing info… I am on Libreoffice 6.2.4.2. I’ll try to make it clearer. I am preparing a database to accept reservations. I built a query where, when run, I am prompted for a starting date and an ending date (a report is generated with all the info in between those two dates. In every place I can imagine I have set my date formats to DD/MM/YYYY, however when I am prompted to enter the starting and ending dates when I run the query it wants the date in the MM/DD/YYYY format. In design view ( for my query) I use > :Earliest for my earliest date and < :Finalle for my ending date.

Ende edit.

[Ratslinger]

Hello,

Cannot present an acceptable answer for the format wanted without additional information - specific LO version, OS and Database being used. SQL can vary depending upon this information. Database dates are stored in YYYY-MM-DD format and entry needs modification to that.

[jodybingo]

Sorry bout the missing info… I am on Libreoffice 6.2.4.2. I’ll try to make it clearer. I am preparing a database to accept reservations. I built a query where, when run, I am prompted for a starting date and an ending date (a report is generated with all the info in between those two dates. In every place I can imagine I have set my date formats to DD/MM/YYYY, however when I am prompted to enter the starting and ending dates when I run the query it wants the date in the MM/DD/YYYY format. In design view ( for my query) I use > :Earliest for my earliest date and < :Finalle for my ending date.

[Ratslinger]

Based on even this latest information, can only repeat the same answer. It does what you want. However you seem to insist on not providing information asked for. See my original comment above. If you want a finished SQL statement then will also need your ENTIRE existing SQL statement and not just pieces. Information in my answer does resolve your situation but may not be specific to your installation. NEED INFO!!!

[jodybingo]

OS is w10. I used design view for the query but here is the SQL (using firebird embedded). Here is some of the sql

SELECT “Contact”.“First Name”, “Contact”.“Phone2”, “Reservation Time & Date”.“Reservation Date”, “Reservation Time & Date”.“Reservation Date”, “Reservation Time & Date”.“Reservation Time” FROM “Reservation Time & Date”, “Contact” WHERE “Reservation Time & Date”.“Contact ID” = “Contact”.“Contact ID” AND “Reservation Time & Date”.“Reservation Date” > :Earliest AND “Reservation Time & Date”.“Reservation Date” < :Finalle

[Ratslinger]

Notwithstanding the lack of information, using HSQLDB you can cast the parameter:

CAST( Substring(:mydate from 7 for 4) || '-' || Substring(:mydate from 4 for 2) || '-' || SUBSTRING(:mydate from 1 for 2) AS "DATE")

Edit 2019-06-06:

Again this is only a guess since information is missing. This is for HSQLDB database. The only difference is that it it a semi-complete SQL statement (fictitious sections) and uses your parameter names:

Select * from blah blah blah where (Reservation Date > CAST( Substring(:Earliest  from 7 for 4) || '-' || Substring(:Earliest  from 4 for 2) || '-' || SUBSTRING(:Earliest  from 1 for 2) AS "DATE")) AND (Reservation Date < CAST( Substring(:Finalle  from 7 for 4) || '-' || Substring(:Finalle  from 4 for 2) || '-' || SUBSTRING(:Finalle  from 1 for 2) AS "DATE"))

This accepts dates entered as dd/mm/yyyy format for this queries parameters.

And again, it matters not in what format you set your dates to display in, the database stores the format as YYYY-MM-DD.

[Ratslinger]

@jodybingo As you have been helped, please help others to know the question has been answered by clicking on the in upper left area of answer which satisfied the question.

[Ratslinger]

@jodybingo Knowing this was a Firebird embedded DB makes it a whole different situation. Since this DB is still somewhat in development, there are a number of kinks to work out. Cast works differently in Firebird and then requires one to bypass the parser. Bypassing the parser does not allow :XXXXX parameters and using ? then has problems. With testing, there are numerous problems in this venue - too many to list.

For your problem, best to file a bug report here → Bugzilla.