DATEPART or WEEKNUM: Access is Denied

Hello y’all,
I new to libreoffice but I do have some experience with SQL so I would like to obtain help from here. My issue is as follow. I have a table that contains a field called TransacDate (which is the date of the transaction). I would like to obtain the week number for each date of transaction. So I wrote the following statement:
SELECT "TransacDate ", DatePart(WW, “TransacDate”) AS TheWeekNum FROM MyTable;
I am receiving the following error message: The data could not be loaded. Access is denied : DATEPART in statement [SELECT "TransacDate ", DatePart(WW, “TransacDate”) AS TheWeekNum FROM MyTable].

I am having the same type of error message when I use WEEKNUM (which perfectly does what I want with LibreOffice Calc)
SELECT "TransacDate ", WEEKNUM(“TransacDate”,1) AS TheWeekNum FROM MyTable.

What am I doing wrong or are these two functions not supported by LibreOffice Base?
Help please.

Hello,

First keep in mind SQL is dependent upon the DB being used. Base is not a database but LO does ship with HSQLDB v1.8 embedded. A function in Calc has nothing to do with SQL.

If using the default DB, WEEK("TransacDate") will work. Newer (v2.x) HSQLDB’s you can also use Extract(WEEK_OF_YEAR from "TransacDate") if you run the SQL command directly.

If this answers your question please tick the :heavy_check_mark: (upper left area of answer). It helps others to know there was an accepted answer.

Thanks for the help. WEEK(“TransacDate”) worked.