Sorting on Dates with boundaries(DB Lib)
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller
Sorting on Dates with boundaries(DB Lib)
Hi All,
I'm trying to implement a data grid which sorts on date boundaries I receive from a database table.
Basically I specify two dates:
From: 04/08/96
To: 05/09/08
I'd like to retrieve the entries in the database which are in between those dates.
Unfortunately, the data base query returns a time as well, so the actual string would be this:
04/08/96 @ 04:15:25 AM
Is there a way to use dbWhere and just have it look for the first word in the database which in this case would just be the date?
I'd like my data grid to be filled up with the dates which meet the above criteria as opposed to all of the *dates* I receive
from the database.
Not sure if I'm being clear or cryptic.
Thanks!
I'm trying to implement a data grid which sorts on date boundaries I receive from a database table.
Basically I specify two dates:
From: 04/08/96
To: 05/09/08
I'd like to retrieve the entries in the database which are in between those dates.
Unfortunately, the data base query returns a time as well, so the actual string would be this:
04/08/96 @ 04:15:25 AM
Is there a way to use dbWhere and just have it look for the first word in the database which in this case would just be the date?
I'd like my data grid to be filled up with the dates which meet the above criteria as opposed to all of the *dates* I receive
from the database.
Not sure if I'm being clear or cryptic.
Thanks!
-
- VIP Livecode Opensource Backer
- Posts: 858
- Joined: Wed Jun 24, 2009 1:17 pm
- Contact:
Re: Sorting on Dates with boundaries(DB Lib)
Hi tasdvl9 - no I think you're being clear 
From your previous queries I think you're pulling data from a remote MySQL database and it sounds like the field you are interested in contains data in the DATETIME format.
First of all I would try a bit more to construct a valid SQL string that can discount the time element (maybe use of asterisks?) - sorry I can't be more specific but I'm no SQL guru and maybe someone else here can advise...
But if you have already pulled down all of the data from a previous query into an array and have it locally then it would take less time for the computer to filter and sort this than to go out and pull a fresh set of data from the server.
Can you run a double repeat loop through your array, find the DATETIME array element, replace it with just the first 8 chars of the element, then run a filter and then a sort - and finally populate the datagrid with the resulting amended array? If you need help constructing the loops let me know and I'll provide an example.
Kind regards
Dave

From your previous queries I think you're pulling data from a remote MySQL database and it sounds like the field you are interested in contains data in the DATETIME format.
First of all I would try a bit more to construct a valid SQL string that can discount the time element (maybe use of asterisks?) - sorry I can't be more specific but I'm no SQL guru and maybe someone else here can advise...
But if you have already pulled down all of the data from a previous query into an array and have it locally then it would take less time for the computer to filter and sort this than to go out and pull a fresh set of data from the server.
Can you run a double repeat loop through your array, find the DATETIME array element, replace it with just the first 8 chars of the element, then run a filter and then a sort - and finally populate the datagrid with the resulting amended array? If you need help constructing the loops let me know and I'll provide an example.
Kind regards
Dave
"...this is not the code you are looking for..."
Re: Sorting on Dates with boundaries(DB Lib)
Thanks, Dave.
I think I'm almost there. I'm just trying to figure out the best way to create a numerical value which I can use
to check my date boundaries.
Presently I am using the convert to dateItems command.
I'm successful at listing the years which fall between say 2008-2013 but still having trouble with the months.
I think I'm almost there. I'm just trying to figure out the best way to create a numerical value which I can use
to check my date boundaries.
Presently I am using the convert to dateItems command.
I'm successful at listing the years which fall between say 2008-2013 but still having trouble with the months.
-
- VIP Livecode Opensource Backer
- Posts: 10052
- Joined: Sat Apr 08, 2006 7:05 am
- Contact:
Re: Sorting on Dates with boundaries(DB Lib)
Comparison is simpler with integers - try converting to seconds.tasdvl9 wrote:Thanks, Dave.
I think I'm almost there. I'm just trying to figure out the best way to create a numerical value which I can use
to check my date boundaries.
Presently I am using the convert to dateItems command.
I'm successful at listing the years which fall between say 2008-2013 but still having trouble with the months.
Richard Gaskin
LiveCode development, training, and consulting services: Fourth World Systems
LiveCode Group on Facebook
LiveCode Group on LinkedIn
LiveCode development, training, and consulting services: Fourth World Systems
LiveCode Group on Facebook
LiveCode Group on LinkedIn
Re: Sorting on Dates with boundaries(DB Lib)
Excellent!!
Thanks! That worked beautifully!
Thanks! That worked beautifully!
-
- VIP Livecode Opensource Backer
- Posts: 858
- Joined: Wed Jun 24, 2009 1:17 pm
- Contact:
Re: Sorting on Dates with boundaries(DB Lib)
Glad it's working for you - and converting to seconds was a much better suggestion 

"...this is not the code you are looking for..."