Ask Your Question

BASIC|HSQLDB: SQL statement vs. wrong data type

asked 2019-10-31 17:42:19 +0200

lonk gravatar image

updated 2019-10-31 17:45:13 +0200

image description

image description

Field type for data from Cell B5 (October 8, 2019) is Date.

Field type for data from Cell B7 is String.

Field type for data from Cell C7 to G7 is Double.

What is the mistake in SQL statement ?

edit retag flag offensive close merge delete



Have just noticed you are cross posting questions without noting this in questions on either post. There is nothing wrong with cross posting unless it is not noted. This is not acceptable as someone could be working on an answer which is already resolved elsewhere.

Also posted here -> [HSQLDB] SQL statement vs Wrong data type

Ratslinger gravatar imageRatslinger ( 2019-11-03 05:51:13 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2019-10-31 18:02:05 +0200

Ratslinger gravatar image


Your date is in an incorrect format. Database dates need to be in the format of YYYY-MM-DD.

You can convert with:

formattedDate = Format(ded, "YYYY-MM-DD")

then use the result for your SQL.

edit flag offensive delete link more


Dear @Ratslinger ,

Thank you so much.

lonk gravatar imagelonk ( 2019-10-31 18:24:39 +0200 )edit

Please, if the answer solves the question click ✔.

m.a.riosv gravatar imagem.a.riosv ( 2019-10-31 23:33:14 +0200 )edit

answered 2019-11-01 07:05:33 +0200

lonk gravatar image

updated 2019-11-01 07:11:50 +0200

For HSQLDB, it must be String.

'   Dim fded As Date            : fded = Format(ded, "yyyy-MM-dd") >> This works in Firebird. 
'   HSQLDB converts the string automatically.
    Dim fded As String          : fded = Year(ded) & "-" & Month(ded) & "-" & Day(ded)

OMG, that's ok.

edit flag offensive delete link more



The database used does not matter. The code used to get the format can be of various types. You have displayed two of them here. Either will work and are not limited to a particular DB. Have used both of these methods (and others) in HSQLDB, Firebird, PostgreSQL, and MySQL. Bottom line is that the date must be in the format of YYYY-MM-DD.

Ratslinger gravatar imageRatslinger ( 2019-11-01 15:08:17 +0200 )edit

BTW - the reason you had a problem with the FORMAT statement is because you DIM'ed fded as a type Date. Had you made that a String as in the other statement it would have worked. In effect you simply reversed what FORMAT had done. You could have also used Format(ded, "YYYY-MM-DD") directly in the SQL statement. The problem had nothing to do with the database type.

Ratslinger gravatar imageRatslinger ( 2019-11-04 04:49:36 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-10-31 17:42:19 +0200

Seen: 110 times

Last updated: Nov 01 '19