Just imported an access database and am designing a query on one of the tables based on a date range. Using the BETWEEN operator is not accepted, for example

SELECT * FROM bs WHERE bs.transdate BETWEEN #01/06/2011# AND #31/05/2012#

Is this not possible in Kexi or is my terminology wrong? I have googled and read the handbook without success.

Hi baaann,

Format #01/06/2011# does not work, ISO format '2011-06-01' would work. BETWEEN..AND operator isn't implemented, feel free to file a wish on bugs.kde.org, it's rather easy to add feature. Good date handling is superimportant.

As a current workaround how about using:

Code: Select all

 bs.transdate >= '2011-01-06' AND bs.transdate <= '2012-05-31'

Hi Jaroslaw

Thanks for the quick reply, your solution works fine. I had assumed the BETWEEN operator was included as it is listed in the Handbook Appendix C and hadn't looked beyond that. I will file a wishlist as you suggest

Ah, so I updated the appendix by clearly stating: the list is a reservation for the future:

http://userbase.kde.org/Kexi/Handbook/A ... ds_for_SQL

Please let me know if this could be better explained or if you need more info.

That makes sense, however is there a resource of currently functioning Kexi sql?

Good Question. Not yet, but the used keywords are always listed in this source code file:
https://projects.kde.org/projects/calli ... tokens.cpp

A wish for documenting the keywords could be filed on bugs.kde.org too

Thanks again Jaroslaw

I'll get the wishes up once I have finished my current project.

Wishlists filed

baaann wrote:Wishlists filed

Thanks, that's the way how we move forward. I am expecting this feature in Kexi 2.9.

Feel free to file more wishes like that.

jstaniek wrote:Good Question. Not yet, but the used keywords are always listed in this source code file:
https://projects.kde.org/projects/calli ... tokens.cpp

A wish for documenting the keywords could be filed on bugs.kde.org too

"Right" and "Left" do not work in Kexi 2.6.2. Do they work in Kexi 2.8?

wmae

wmae wrote:"Right" and "Left" do not work in Kexi 2.6.2. Do they work in Kexi 2.8?

No they don't.
They were meant for a "LEFT/RIGHT JOIN" construct but that is unfinished.
Or, if you mean LEFT/RIGHT(text, number) function, this is missing as well. Actually much easier to add than JOINs in a portable way (across db backends).

Filling two wishes would be a good idea.

The above suggested syntax used to work (Kexi 2.x) but not any more. Has something changed in Kexi 3.1?
I tried the following:
SELECT date, odometer, mileage FROM visitation WHERE date >= '2017-01-01' AND date <= '2017-12-31'
and get this error:
Expression "date >= '2017-01-01'" requires compatible types of arguments. Specified arguments are of type Date and Text.
What am I doing wrong?

theraponsundoulos wrote:The above suggested syntax used to work (Kexi 2.x) but not any more. Has something changed in Kexi 3.1?
I tried the following:
SELECT date, odometer, mileage FROM visitation WHERE date >= '2017-01-01' AND date <= '2017-12-31'
and get this error:
Expression "date >= '2017-01-01'" requires compatible types of arguments. Specified arguments are of type Date and Text.
What am I doing wrong?

From version to version we're more precise regarding type checking. So comparison you're referring to worked by accident in SQLite (.kexi files), typically through implicit conversion, but it may fail for other backends. Of course it's KEXI that performs more checks, SQLite still allows the construct as SQLite is largely "typeless", for good and bad aspects of that. For SQLite date/time types are strings.

I am sorry for the problem. It's apparent we need to bring a quick (for 3.1.1) and proper solution for at least these:

- specifying date/date-time/time constants
- conversions to these types

Possible grammar would be as follow.

Constants:

SELECT CAST('2014-09-13' AS DATE);
SELECT CAST('2014-09-13T12:30:00' AS DATETIME);

This would work for conversion of columns too:

SELECT CAST(sometextcolumn AS DATE) from T;

In case of SQLite, CAST would be superset of its internal CAST expression (http://www.sqlite.org/lang_expr.html#castexpr).

We can consider more direct MS-Access/JET based notation as an option, what was included in the original question too:
#2014-09-13#
#2014-09-13 12:30:00#

It's quite readable and we would not deviate from standard because there's no standard for date/time in SQL...

For completeness, CAST(domedatecolumn AS TEXT) would correctly convert to text so this would work too:

CAST(domedatecolumn AS TEXT) <> '2014-09-13'

But would be more typing than

domedatecolumn <> #2014-09-13#

For status and shape of the SQL functions supported in KEXI please visit https://community.kde.org/Kexi/Plugins/ ... _Functions.
Any ideas and opinions welcome!
J.

Got it. Thanks.

Reported at https://bugs.kde.org/show_bug.cgi?id=393094, scheduled for 3.1.1.