Page 1 of 1

[Solved] [HSQLDB] SQL statement vs Wrong data type

PostPosted: Fri Nov 01, 2019 6:30 am
by oonk
What is the mistake in SQL ?
Screenshot_20191101_111827.jpg

Screenshot_20191101_111956.jpg

Screenshot_20191101_114600.jpg

Re: BASIC|HSQLDB| SQL statement vs Wrong data type

PostPosted: Fri Nov 01, 2019 6:48 am
by FJCC
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.

Re: BASIC|HSQLDB| SQL statement vs Wrong data type

PostPosted: Fri Nov 01, 2019 7:34 am
by oonk
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.

Re: [Solved] [HSQLDB] SQL statement vs Wrong data type

PostPosted: Sun Nov 03, 2019 6:53 am
by Ratslinger
This was also posted and answered here -> https://ask.libreoffice.org/en/question ... data-type/