No Count with DateTime<='2017-7-22 23:59' and DateTime>='

Creating desktop or client-server database solutions?

Moderators: Klaus, FourthWorld, heatherlaine, robinmiller, kevinmiller

Post Reply
mrcoollion
Posts: 343
Joined: Thu Sep 11, 2014 1:49 pm
Location: The Netherlands

No Count with DateTime<='2017-7-22 23:59' and DateTime>='

Post by mrcoollion » Sat Jul 22, 2017 11:41 am

Question.
I added DateTime <= '2017-7-22 23:59' and DateTime >= '2017-6-10 00:00' to the working query below but after adding this part the query delivers a 0 record count and it should be 4.
Working query which counts records (4): SELECT COUNT('L0_NodeLabel') as 'L0_NodeLabel' FROM 'Registrations' WHERE IssueType LIKE '%' AND Status LIKE '%' AND L0_NodeLabel = 'Root 1' AND IssueYN LIKE '%'

After adding the DateTime Column part:
Not working as expected query which countt 0 records (0) but should count 4: SELECT COUNT('L0_NodeLabel') as 'L0_NodeLabel' FROM 'Registrations' WHERE DateTime <= '2017-7-22 23:59' AND IssueType LIKE '%' AND Status LIKE '%' AND DateTime >= '2017-6-10 00:00' AND L0_NodeLabel = 'Root 1' AND IssueYN LIKE '%'

The DateTime Column is a TEXT type Column.

Can someone enlighten me in why the DateTime filter does not work?

Regards,

Paul

bogs
Posts: 2376
Joined: Sat Feb 25, 2017 10:45 pm

Re: No Count with DateTime<='2017-7-22 23:59' and DateTime>=

Post by bogs » Sat Jul 22, 2017 12:06 pm

If I had to guess, I would say that it was because dateTime is a sorting function, not one that could be used for testing as your trying to use it?

From the dictionary -
Summary
Used with the sort and sort container commands to sort by date and/or time.
---------------
Description
... Use the dateTime keyword when sorting by a field or portion
of a container that is a date or time.
... Alphabetical sorting does not take the special format of dates and times
into account. For example, a normal sort places 11:30 AM after 2:00 AM,
because 2 is greater than 1. The dateTime keyword recognizes all
LiveCode's date and time formats and sorts
them in time order, rather than alphabetical or numeric order.
Again just a guess here, but for what your trying to determine, I'd think you'd rather be using the 'date' function for the comparison, possibly with the internet argument, or the short date & short time concatenated, then do the comparison.

*Edit - of course, if you are trying to just sort those 4 items, and I simply misunderstood, please ignore this :)
Image

mrcoollion
Posts: 343
Joined: Thu Sep 11, 2014 1:49 pm
Location: The Netherlands

Re: No Count with DateTime<='2017-7-22 23:59' and DateTime>=

Post by mrcoollion » Sat Jul 22, 2017 1:24 pm

Sorry bogs I made a mistake :lol: .

DateTime <= '2017-7-22 23:59' and DateTime >= '2017-6-10 00:00' should have had a 0 before the month 7 and month 6. This is why it did not work as expected.

Now it works.

An alternative is to use the syntax for datetime comparisson: <where mydate >= Datetime('2009-11-13 00:00:00') and mydate <= Datetime('2009-11-15 00:00:00')>
This also worked fine.
Just some additional information for those who are interested:
A time string can be in any of the following formats:
YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
YYYY-MM-DDTHH:MM
YYYY-MM-DDTHH:MM:SS
YYYY-MM-DDTHH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
now
DDDDDDDDDD

Some reference links:
https://sqlite.org/lang_datefunc.html
https://stackoverflow.com/questions/197 ... comparison
https://www.w3schools.com/sql/sql_where.asp

bogs
Posts: 2376
Joined: Sat Feb 25, 2017 10:45 pm

Re: No Count with DateTime<='2017-7-22 23:59' and DateTime>=

Post by bogs » Sat Jul 22, 2017 1:35 pm

Heh, looks like we're both sorry, I had gone to do further research and it looks like I failed to recognize you were using the sql datetime, not the Lc dateTime :oops: so I did completely misunderstand the situation :(

Glad you sorted it out though, and that you posted a fuller explanation of your findings :)
Image

Post Reply

Return to “Databases”