Ask Your Question
0

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

Comments

@lonk,

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
0

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

Ratslinger gravatar image

Hello,

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

Comments

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
0

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. 
'   https://stackoverflow.com/questions/13141328/how-do-i-create-a-specific-date-in-hsqldb
'   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

Comments

@lonk,

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

Stats

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

Seen: 110 times

Last updated: Nov 01 '19