Apache OpenOffice Community Forum

What is the mistake in SQL ?

Your last image seems to show that EffectiveDate is being passed as the string '10/08/2019' and that is not a valid date format for HSQLDB, if I remember correctly. I think it must have the format 2019-10-08 or maybe 2019-10-08 00:00:00. I see that in your code you try to set the format but the error message suggests that is not working as expected.

FJCC wrote:Your last image seems to show that EffectiveDate is being passed as the string '10/08/2019' and that is not a valid date format for HSQLDB, if I remember correctly. I think it must have the format 2019-10-08 or maybe 2019-10-08 00:00:00. I see that in your code you try to set the format but the error message suggests that is not working as expected.

That works with embedded Firebird of LibreOffice Base.

Thank you so much for your recommendation.
Finally, I found the way for HSQLDB.

Code: Select all   Expand viewCollapse view

'   https://stackoverflow.com/questions/13141328/how-do-i-create-a-specific-date-in-hsqldb
'       HSQLDB converts the string automatically.
   Dim fed As String         : fed = Year(ed) & "-" & Month(ed) & "-" & Day(ed)

That's ok.

This was also posted and answered here -> https://ask.libreoffice.org/en/question ... data-type/