Ah! Very useful thank you! Bob
On Jul 20, 2011, at 10:50 AM, Pete wrote: > Hi Bob, > Depending on your requirements, your tests might ned to be >= and <=. Also, > SQL has a BETWEEN operator that would make your code a little more > readable: > > "SELECT * FROM xyz WHERE startDate BETWEEN savedStartDate AND > savedEndDate...". > > I don't think you need to store your dates in julian format. The standard > SQL storage format of YYYY-MM-DD should work for your comparisons as long as > startdate is in that format also (I think). If not, SQL (at least sqlite) > has date conversion expressions you can use to convert the dates to Julian > before the comparison: > > SELECT * FROM xyz WHERE strftime(%J,startDate) BETWEEN > strftime(%J,savedStartDate) AND strftime(%J,savedEndDate)..." > > mySQL does not have a Julian day conversion expression, but it does have a > TO_DAYS function so, as long as your dates are not before 1582 (!!!): > > "SELECT * FROM xyz WHERE TO_DAYS(startDate) BETWEEN TO_DAYS(savedStartDate) > AND TO_DAYS(SavedEndDate)..." > > For a system that is supposed to adhere to a standard, SQL has a remarkable > number of variations in its various implementations! > > > Pete > Molly's Revenge <http://www.mollysrevenge.com> > > > > > On Wed, Jul 20, 2011 at 9:32 AM, Bob Sneidar <b...@twft.com> wrote: > >> Hey as long as we are talking about dates and times, I am going to need >> soon a method to determine if a block of time intersects with another. >> Specifically I will need to do this in an SQL query. I suppose the best way >> would be to store all dates in julian format, then have a query that, given >> savedStartDate and savedEndDate are columns in a table, goes something like >> : >> >> select * where (startDate > savedStartDate and startDate < savedEndDate) or >> (endDate > savedStartDate and endDate < savedEndDate) >> >> Is that about right? Any other ideas? >> >> Bob >> >> >> On Jul 20, 2011, at 8:29 AM, James Hurley wrote: >> >>> Roger >>> >>> Is this what you are looking for? >>> >>> on mouseUp >>> set itemdel to ":" >>> put the long time into tTime >>> put char 1 to -4 of tTime into field 1 >>> convert tTime to dateItems >>> set the hms of me to tTime >>> countDown >>> end mouseUp >>> >>> on countDown >>> put the hms of me into temp >>> subtract 1 from item -2 of temp >>> --The dateItems will take care of the negative seconds >>> convert temp to dateItems >>> set the hms of me to temp >>> put temp into tDisplayTIme >>> put item -4 to -2 of tDisplayTIme into temp2 >>> replace "," with ":" in temp2 >>> put temp2 into field 1 >>> send countDown to me in 1 sec >>> --Change the "1 sec" to "1 Minute" when you are sure it works for you. >>> end countDown >>> >>> Jim Hurley >>> >>> >>> _______________________________________________ >>> use-livecode mailing list >>> use-livecode@lists.runrev.com >>> Please visit this url to subscribe, unsubscribe and manage your >> subscription preferences: >>> http://lists.runrev.com/mailman/listinfo/use-livecode >> >> >> _______________________________________________ >> use-livecode mailing list >> use-livecode@lists.runrev.com >> Please visit this url to subscribe, unsubscribe and manage your >> subscription preferences: >> http://lists.runrev.com/mailman/listinfo/use-livecode >> >> > _______________________________________________ > use-livecode mailing list > use-livecode@lists.runrev.com > Please visit this url to subscribe, unsubscribe and manage your subscription > preferences: > http://lists.runrev.com/mailman/listinfo/use-livecode _______________________________________________ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
