Right syntax for SQL date parameter in function (SOLVED)

[fifou64]

Hello,

I am using LO base 6.3.5.2 with an HSQLDB.

I have an SQL request which works and looks like this :

SELECT .... (attributes)
FROM myTable, [..](tables)
WHERE  ....(relationships)
AND "myTable"."date" BETWEEN {d '2018-01-01' } AND {d '2018-12-31' }

Now I want to introduce a parameter (:endOfYearDate) to let the user choose the date.

If I replace the last line of my formula by the following, it still works :

AND YEAR("myTable"."date") = YEAR(:endOfYearDate)

But if I use a function on the date parameter only, I can’t make this work :

AND "myTable"."date" BETWEEN DATE_SUB ( :endOfYearDate, INTERVAL 12 MONTH ) AND :endOfYearDate

I tried some variations but none of them would work.
Can you give me the working syntax on this one ? I tried the CONCAT (YEAR(:endOfYearDate),… doesn’t work either.

Thanks.

[Ratslinger]

Hello,

Will base this answer on the more likely database of HSQLDB embedded - that comes with LO and is v1.8

DATE_SUB is not available in that version of the database. You can find it in later versions ( ie: 2.x)

This tested as working in HSQLDB embedded:

"myTable"."date" BETWEEN CAST( YEAR( :endOfYearDate ) - 1 || RIGHT( :endOfYearDate, 6 ) AS CHAR ( 10 ) ) AND :endOfYearDate

If this is a split database with HSQLDB v2.x then there are other complications. DATE_SUB is not recognized in the Base Query editor unless Run SQL directly is on and thus negates using parameters. The statement above does not work without further modifications. For this case it is much easier to use both a starting and an ending date.

[fifou64]

Thank you very much! Yes it is the embedded HSQLDB.

I ended using :

AND "myTable"."date" BETWEEN CAST( YEAR( :endOfYearDate ) || '-01-01' AS CHAR ( 10 ) ) AND :endOfYearDate

Indeed, your solution gave the date between the (YYYY-1)-12-31 and the YYYY-12-31 while I want the date between the YYYY-01-01 and the YYYY-12-31.

I didn’t know about the database version. Is it recommended to switch to another version?

[Ratslinger]

@fifou64,

There is no reason to close a question. Others may at some time have an even better answer.

To note an answer as accepted, please tick the (upper left area of answer). It helps others to know there was an accepted answer.

Whatever database you use is your decision. I will point out that, as you can see with lack of functions, HSQLDB embedded is old. It is prone under certain conditions to lose data. There are newer versions with more functionality. There are also different databases such as Firebird embedded (still being worked upon in some regards but usable) and server based such as MySQL, PostgreSQL, MariaDB and many more.

[Ratslinger]

@fifou64,

The statement presented can be even easier if you always want from 01-01 thru 12-31. Just enter a year wanted:

AND "myTable"."date" BETWEEN :endOfYearDate || '-01-01' AND :endOfYearDate || '-12-31'

No need to even cast.