BASIC|HSQLDB: SQL statement vs. wrong data type

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 ?


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


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.

Dear @Ratslinger ,

Thank you so much.

Please, if the answer solves the question click :heavy_check_mark:.

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.


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.

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.