Expected date format in BASE query with JOIN

[DanJohnstonCanada]

I’m using LibreOffice version 7.3.7.3

Using BASE with several tables. Over 10,000 records in some tables.
Several tables contain dates, and these dates are in the 1700s, so all date fields in the tables, and in the forms that display the tables and sub-tables, have been set to use the “YYYY-MM-DD” as their default format.

I can create a simple query of the main table, i.e. simply output all the data in this table, and you get the expected output for the two date fields.

Now JOIN this table with another to add in names associated with the records, and add code to remove duplicate information in the output for the second (and third, and…) person.

Note that the two date columns are displayed as a number, not a date.

I realize that I can simply right click on the column(s) and change the format to the required DATE format, but you need to remember to do this every time.

What is different between the two examples that would change the output?

As a related question; How do you define a NULL date?

Thanks for your help!

[RobertG]

Formatting of queries won’t be saved at this moment. See 86315 – EDITING: Formatting of queries should be saved by base.

If it is a simple query Base could detect the right format from the table. Format of a query will be saved.

@DanJohnstonCanada

What is different between the two examples that would change the output?

I see only one example in your post, so I don’t understand what you mean with this.

NULL: The field is empty, no date is set.

[Ratslinger]

Hello,
The display output can be shown as a date if you CAST the field such as:

CASE WHEN "NameID" > 1 THEN NULL ELSE CAST("StartDate" AS DATE) END AS "StartDate"

[DanJohnstonCanada]

“Ratslinger” asked why I didn’t show two examples as my post suggested.
Answer: I’m a new user and the system would not allow me to have two media attachments.
I had to delete one - the least useful one.

Second: Your solution to CAST the date back to a DATE (which it already is) did not work for me when I tried it, and it still does not work as you can see in the SQL code and query output below.

Thanks for the suggestion however,

[Ratslinger]

@DanJohnstonCanada

“Ratslinger” asked why I didn’t show two examples as my post suggested.

No, that was not I.
.
CAST works for me. What database are you using? Can you post a sample redacted Base file?

[Ratslinger]

@DanJohnstonCanada

Second: Your solution to CAST the date back to a DATE (which it already is) did not work for me when I tried it, and it still does not work as you can see in the SQL code and query output below.

Yes that is my fault. I forgot that I turned on Run SQL command directly. You do not need the CAST for this to work.
.
Without run directly:

.
With run directly on:

[Villeroy]

The naked Base grid shows calculated dates as integer day numbers. The number format of a date is completely unrelevant as long as the values are right. In a form or report you can format the values anyway you want. The underlying query is never shown to the database user.

[DanJohnstonCanada]

Ratslinger;

You pointed out to me something else I didn’t know (There are LOTS of things I don’t know)

I was always using the “Run Query” button/icon (F5).

I never used the “Run SQL Command directly” button.

When I did use the “Run SQL …” button the date fields (without CAST) did show up as dates.

The date columns were in MM/DD/YY format, but they WERE dates.

I guess I will have to study what is the difference between these two methods to run the query.

And, perhaps I can then put in commands to get the 4 digit year date format that I need.

At least I have a place to look for a solution to this minor problem that has been bugging me for the past week or so.

Dan

[DanJohnstonCanada]

I followed up on the “run SQL direct” option and found previous posts about using “Reports” instead of “Query”.
I used the wizard to create a report from the query I have (right click on the query name). You then get a report with the same joined table data in it.
HOWEVER…
It takes MUCH longer to create the report (2-3 minutes and almost 300 pages from my 10,000 plus records)
The date shown in the report WAS in the required YYYY-MM-DD format - without any change to my SQL code
But, the extra page formatting on the report does not permit the data to be easily copied to other files such as spreadsheets. (Note: If you run the query and get numbers instead of dates, then copy to a spreadsheet (calc) file, the date columns are displayed as numbers. If you then format the calc columns to DATE, the year seems to be wrong. Change format on the query columns to proper date first (with 4 digit year) and then copy to the spreadsheet file.)
So…
Still lookin for a ‘final’ solution.
Perhaps I will just wait for a new release. (Has anyone tried something like this with the latest Developer Version of LibreOffice?)

[Ratslinger]

@DanJohnstonCanada
Please only use Answer to answer the original question, otherwise use a comment.
.
It is usually better to create a View for use in a report. Typically faster. Also, the report output can be to a Calc spreadsheet directly:

.
You really are not stating just what this final solution is.

[DanJohnstonCanada]

What is my “final solution”?

I have created a BASE database to store/add/filter/export historical records. It currently contains 11,318 records in the “main” table - and increasing. There are ‘many’ images of original source documents for some of these records, many records refer to the same ship, many people in some main records, multiple references per ship or per person, etc, So, I created sub-tables (3) that are related to the main table, and several sub-sub-tables (5) that are connected to some of these sub-tables. It is a very expandable, fast, compact way to hold and add to these records.

However…
LibreOffice BASE is not the only software out there. Sometimes it is needed to search/sort this date using sophisticated text analysis software that can also search internet archives for connections or related information. This type of software can usually read comma separated data - or sometimes even read (Excel) spreadsheet files directly. B.T.W. I am using BASE because Excel doesn’t work well with dates below 1900.

So, to answer your question;
The purpose of the query is to re-construct a ‘spreadsheet compatible’ file of the data, in a format that resembles the original hand written records, so that it can be saved in a format that is usable by this other software.
The query I showed used only the main table and one sub-table to add names to the records. I have a more complicated version with multiple JOINs that collects parts of the data from three sub-tables and merges this with one of my two main tables,

All this seems to work OK, as long as I remember to fix the format of the date columns in the query output. before I copy the data to a spreadsheet file.
I was just thinking there should be a simple reason why the output of a simple query can get the format to match the default set in the table(s), but a query with a JOIN between tables displays the date information as a number. Is it a bug, or a limitation of the developer (me).

Thanks for your hints. I have much to learn about ‘Report Views’, and many other things.

Dan