DATEPART or WEEKNUM: Access is Denied

[EvadoNad]

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.

[Ratslinger]

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 (upper left area of answer). It helps others to know there was an accepted answer.

[EvadoNad]

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