LiveCode Forums.

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!

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

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.

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.

Comparison is simpler with integers - try converting to seconds.

Excellent!!

Thanks! That worked beautifully!

Glad it's working for you - and converting to seconds was a much better suggestion