WEEK, WEEKDAY and YEARDAY do not work when entered in the Query Design dialog.
Entering the following using the Query Design dialog:
EXTRACT( WEEK FROM "DateVisit" )
Returns the following error: Expression, field name 'EXTRACT( WEEK FROM "DateVisit" )' does not exist
However, the following statement works (for WEEK, WEEKDAY and YEARDAY) when entered in Tools -> SQL
EXTRACT( WEEK FROM "DateVisit" ) "WeekNo"
WHERE EXTRACT( WEEK FROM "DateVisit" ) = 1
Confirmed in today's build of 6.1 RC and 6.2Alpha0
The extract function will work without SQL Direct enabled but is not recognized in the designer UI.
1 Download this example file
2 Create a new query with the SQL view and enter the following Select statement.
COALESCE("FirstName", ' ', "LastName") As "Name",
EXTRACT(YEAR FROM "OrderDate") As "Year",
COUNT(*) As "Count"
FROM "Employees" INNER JOIN "Orders"
GROUP BY "Country", "Name", "Year"
3 Run the query (zero records because one of the tables is empty)
4 switch views to the designer UI
5 Unable to run the query or even switch back to SQL view.
Dup of tdf#104986?
Hi. It was reported as fixed in that build, but it isn't for fixed for me. I texted it again in RC2. Still not working. Interestingly, I can no longer create a FIREBIRD db in RC2.
Just to add, that you may be pointing out that I should've replied to that previous thread. Didn't find it - sorry1
(In reply to Vilos from comment #3)
> Hi. It was reported as fixed in that build, but it isn't for fixed for me. I
> texted it again in RC2. Still not working. Interestingly, I can no longer
> create a FIREBIRD db in RC2.
You need to enable the experimental features first. Tools - Options - Advanced - Enable experimental features..
I have set the bug's status to 'NEEDINFO'. Please change it back to
'UNCONFIRMED' if the bug is still present in the latest version.
Created attachment 143720 [details]
Querydesigner screen shot
Simple case is working for me on Ubuntu 18.04 and build (QueryDesigner Screenshot)
Build ID: 2466ea26c4bef1e002a24f6845084633e5a058c4
CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk2;
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:libreoffice-6-1, Time: 2018-07-21_22:43:36
Locale: en-US (en_US.UTF-8); Calc: group threaded
and 6.2 Master
Created attachment 143721 [details]
Design View for WEEKDAY and YEARDAY error and LO Version
Tools - Options - Advanced - Enable experimental features set. WEEK works. WEEKDAY and YEARDAY don't. See attached image. Just to re-iterate, the SQL equivalent statements work in Tools->SQL.
Sorry, twice in two days I couldn't see the tree for the forest or some such.
MILLISECOND is also not recognized by the Base SQL parser.
I think the EXTRACT function needs to be added to the SQL grammar of LibreOffice