Reply to topic

Querying by date range

baaann
Registered Member
Posts
7
Karma
0
OS

Querying by date range

Wed Feb 19, 2014 4:40 pm
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.
User avatar jstaniek
Moderator
Posts
922
Karma
2
OS

Re: Querying by date range

Wed Feb 19, 2014 9:06 pm
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'


Best regards,
Jarosław Staniek
Image

KEXI - Open Source Visual DB Apps Builder
• Request feature or fix for KEXI here
May I help you? Please mention your app's version and OS when asking for help
baaann
Registered Member
Posts
7
Karma
0
OS

Re: Querying by date range

Wed Feb 19, 2014 9:28 pm
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
User avatar jstaniek
Moderator
Posts
922
Karma
2
OS

Re: Querying by date range

Wed Feb 19, 2014 11:53 pm
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.


Best regards,
Jarosław Staniek
Image

KEXI - Open Source Visual DB Apps Builder
• Request feature or fix for KEXI here
May I help you? Please mention your app's version and OS when asking for help
baaann
Registered Member
Posts
7
Karma
0
OS

Re: Querying by date range

Thu Feb 20, 2014 10:41 am
That makes sense, however is there a resource of currently functioning Kexi sql?
User avatar jstaniek
Moderator
Posts
922
Karma
2
OS

Re: Querying by date range

Thu Feb 20, 2014 10:49 am
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 :)


Best regards,
Jarosław Staniek
Image

KEXI - Open Source Visual DB Apps Builder
• Request feature or fix for KEXI here
May I help you? Please mention your app's version and OS when asking for help
baaann
Registered Member
Posts
7
Karma
0
OS

Re: Querying by date range

Thu Feb 20, 2014 1:56 pm
Thanks again Jaroslaw

I'll get the wishes up once I have finished my current project.
baaann
Registered Member
Posts
7
Karma
0
OS

Re: Querying by date range

Fri Feb 28, 2014 2:50 pm
Wishlists filed :)
User avatar jstaniek
Moderator
Posts
922
Karma
2
OS

Re: Querying by date range

Tue Mar 04, 2014 10:33 am
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.


Best regards,
Jarosław Staniek
Image

KEXI - Open Source Visual DB Apps Builder
• Request feature or fix for KEXI here
May I help you? Please mention your app's version and OS when asking for help
wmae
Registered Member
Posts
32
Karma
0

Re: Querying by date range

Thu Mar 06, 2014 3:48 pm
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
User avatar jstaniek
Moderator
Posts
922
Karma
2
OS

Re: Querying by date range

Sat Mar 08, 2014 8:31 pm
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.


Best regards,
Jarosław Staniek
Image

KEXI - Open Source Visual DB Apps Builder
• Request feature or fix for KEXI here
May I help you? Please mention your app's version and OS when asking for help
theraponsundoulos
Registered Member
Posts
4
Karma
0

Re: Querying by date range

Wed Apr 11, 2018 2:18 pm
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?
User avatar jstaniek
Moderator
Posts
922
Karma
2
OS

Re: Querying by date range  Topic is solved

Wed Apr 11, 2018 6:38 pm
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.


Best regards,
Jarosław Staniek
Image

KEXI - Open Source Visual DB Apps Builder
• Request feature or fix for KEXI here
May I help you? Please mention your app's version and OS when asking for help
theraponsundoulos
Registered Member
Posts
4
Karma
0

Re: Querying by date range

Thu Apr 12, 2018 1:13 am
Got it. Thanks.
User avatar jstaniek
Moderator
Posts
922
Karma
2
OS

Re: Querying by date range

Fri Apr 13, 2018 9:12 am
Reported at https://bugs.kde.org/show_bug.cgi?id=393094, scheduled for 3.1.1.


Best regards,
Jarosław Staniek
Image

KEXI - Open Source Visual DB Apps Builder
• Request feature or fix for KEXI here
May I help you? Please mention your app's version and OS when asking for help

 
Reply to topic

Bookmarks



Who is online

Registered users: Baidu [Spider], Bing [Bot], boospy007, dhealey, Exabot [Bot], Google [Bot], mx, savornicesei, Yahoo [Bot]