Firebird database in Base.
EMP table with “Name”, “Address” field (both varchar) and “Startdate”. Made a query on it to select all employees which started less than a year ago (so a condition on “Startdate”. Thus raised a problem with run sql directly which was resolved in my previous question Run report based on query with direct run sql .
During that I had made a view based on this quey.
Now making a report with the wizard, based on this query and displaying the “Startdate” field, displays this field as a number, not as a date. Replacing the SQL command in the report data properties with the reference to the query does not change that.
But making the report with the wizard basing on the view i.s.o. the query displays the “Startdate” correctly in the report as a date.
Is this expected behavior or worth a bug report?
In the edit mode of the report open the controls properties. In the Data tab you can set the formatting for the date.
That is exactly what I wanted to avoid doing. I cann’t see any good reason why the report wizard should produce a different result if generating the report from a query or a view. So, I’ll take the time to report a bug on this.
So I’m going to take the time to report an error on this.
You’re welcome to do that. But it will not be very successful.
First of all, a date and time in Calc is always a number in the system and it has to be formatted if needed.
See: DATE
That date-time in reality a number is, is a '“universal” truism. But that’s no reason for any software whatsoever to display it as a number when the same software allows an item to be defined at the user interface as a date type.
…when the same software allows an item to be defined at the user interface as a date type.
Yes, you may be right, but everyone may want different formatting.
And if you already know everything anyway, why ask you here?
You might as well write a bug report!
It’s unfortunate that when Bug reports are created the reference is not made available to all. This one is filed as tdf#139315.
I appears from the post (have not tested) that this is SQL that will work:
SELECT * FROM "EMP" WHERE WHERE "startdate" > CAST( CURRENT_DATE AS DATE ) - 365
Fortunately those of us answering questions have no qualms about sharing information.
This is not the only project I’m working on, so I simply had no time to come back here and post the bug number.
As far as the proposes SQL goes: I came around something similar in another post, but I simply refused to use cast on a field and expression which are both of the correct type. I appreciate LO Base a lot (having to maintain another project in MS-Access), but it eventually should do things correctly. I don’t mind putting up test cases if required.
To ebot: I don’t know everything, otherwise I would be running around here.
And I agree, if I would want some format different from what is set in the LO options, then I would have to put up some mask. But this is not the case here. I’m content using the “default” based on the LO options and that should work without further fiddling. Basically I am trying to avoid as much as I can special cases. If the “defaults” run correctly, then afterwards I would consider changing format masks etc…
Based on statements from one of my past supervisors:
There is never enough time to do things right to begin with but there always seems to be time to fix the problems created.