Date intersection revisited

[Bob Sneidar]

Oh cool! Well then of course I would have to conditionally switch the query depending on the type of database I was using. The technique I am employing however precludes that to some degree. I am storing queries in properties using placeholder delimiters. Anything that comes between two percentage signs gets replaced with the value of it at runtime. This means I can insert a variable in a query like %theStartDate% and then programmatically replace it at runtime. It also means I can put things like %theRecordA["building"] & theRecordA["roomnumber"]% in the stored query and it will evaluate to the concatenated value at runtime. It is turning out to be a really powerful method, but I want to keep the stored queries from becoming too complex. 

I could also have used CONCAT(%theRecordA["building"]%,%theRecordA["roomnumber"%) I suppose, but there are lots of Livecode statements I can use that have no equivalent in SQL, and by using Livecode statements whenever I can I am not database type dependent. The goal is to give developers in the application framework I am developing in parallel to the app I am coding, a way to "hook" into their own code when building queries without having to hard code them. 

Bob


On Jan 11, 2012, at 7:55 PM, Pete wrote:

> You can do this with sql functions too Bob.  The sqlite date function has a
> modifier parameter, eg date(2011-1-11,'+ 1 DAY).  MYSQL has the DATE_ADD
> and DATE_SUB functions which do the same thing.
> Pete