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

Creating a macro - Writing a Script - Using the API

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

Postby oonk » Fri Nov 01, 2019 6:30 am

What is the mistake in SQL ?
Screenshot_20191101_111827.jpg

Screenshot_20191101_111956.jpg

Screenshot_20191101_114600.jpg
Last edited by Hagar Delest on Fri Nov 01, 2019 12:16 pm, edited 1 time in total.
Reason: tagged solved
| Fedora 31 Workstation KDE of Fedora from Spins | LibreOffice 6.2.8.2-2 | Base with embedded HSQLDB |
oonk
 
Posts: 15
Joined: Fri Oct 04, 2019 8:03 am
Location: Pathum Thani, THAILAND

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

Postby FJCC » Fri Nov 01, 2019 6:48 am

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.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7392
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

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

Postby oonk » Fri Nov 01, 2019 7:34 am

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.
| Fedora 31 Workstation KDE of Fedora from Spins | LibreOffice 6.2.8.2-2 | Base with embedded HSQLDB |
oonk
 
Posts: 15
Joined: Fri Oct 04, 2019 8:03 am
Location: Pathum Thani, THAILAND

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

Postby Ratslinger » Sun Nov 03, 2019 6:53 am

This was also posted and answered here -> https://ask.libreoffice.org/en/question ... data-type/
LibreOffice 6.1.3
OpenOffice 4.0.1
Ubuntu Mate 18.04
Mint 18.3
Ratslinger
 
Posts: 22
Joined: Sun Mar 01, 2015 3:34 am


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 4 guests