Sorting on Dates with boundaries(DB Lib)

Got a LiveCode personal license? Are you a beginner, hobbyist or educator that's new to LiveCode? This forum is the place to go for help getting started. Welcome!

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller

Post Reply
tasdvl9
Posts: 94
Joined: Fri Dec 06, 2013 3:55 am

Sorting on Dates with boundaries(DB Lib)

Post by tasdvl9 » Tue Apr 15, 2014 10:50 pm

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!

dave.kilroy
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 858
Joined: Wed Jun 24, 2009 1:17 pm
Contact:

Re: Sorting on Dates with boundaries(DB Lib)

Post by dave.kilroy » Wed Apr 16, 2014 12:01 am

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
"...this is not the code you are looking for..."

tasdvl9
Posts: 94
Joined: Fri Dec 06, 2013 3:55 am

Re: Sorting on Dates with boundaries(DB Lib)

Post by tasdvl9 » Wed Apr 16, 2014 6:30 pm

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.

FourthWorld
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 10052
Joined: Sat Apr 08, 2006 7:05 am
Contact:

Re: Sorting on Dates with boundaries(DB Lib)

Post by FourthWorld » Wed Apr 16, 2014 6:36 pm

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.
Richard Gaskin
LiveCode development, training, and consulting services: Fourth World Systems
LiveCode Group on Facebook
LiveCode Group on LinkedIn

tasdvl9
Posts: 94
Joined: Fri Dec 06, 2013 3:55 am

Re: Sorting on Dates with boundaries(DB Lib)

Post by tasdvl9 » Wed Apr 16, 2014 7:27 pm

Excellent!!

Thanks! That worked beautifully!

dave.kilroy
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 858
Joined: Wed Jun 24, 2009 1:17 pm
Contact:

Re: Sorting on Dates with boundaries(DB Lib)

Post by dave.kilroy » Wed Apr 16, 2014 7:47 pm

Glad it's working for you - and converting to seconds was a much better suggestion :)
"...this is not the code you are looking for..."

Post Reply