How can I compare dates in if statements within format of DBQUERY and sort dates accordingly??

fI desperately try to compare two dates in an IF - statement to achieve an action accordingly, wheras one is in a variable.

Used time fomat in variable is "22 Apr 2013"

Variable is called %DATUM% and is defined by using a date stored in a meta preference data

Now I try to get a result depending if a date is in a certain timeframe.

Example: if %DATUM% is between now and the next two days I want to get result A

%IF{"' '%DATUM%' > now AND 'now + 2*86400' " then="A"}%

86400 is one day in seconds

I tried many things using CALC, d2n and combinations of these.

I use it within DBQuery format string such as:

%IF{"d2n('$expand(preferences.due)') > d2n('%DATE%') AND d2n('$expand(preferences.due)') <= %CALC{"$TIMEADD($TIME(),2,day)"}%" then="A"}%

while due is a META::PREFERENCE and contains dates in format '24 May 2013' variable

Maybe it is a matter of how DBQUERY works.

When I want to sort dates like sort="preference.due" the result ist just sorted after the first two figures but NOT the date itself. Any solution?

If somebody could resolve this issue I would be very happy.

-- ThomasSchmidt - 24 Apr 2013

It appears that part of your problem is mixing up the DBQuery query syntax with the format syntax. Although "preferences.due='...'" is a valid query, $expand(preferences.due) is not valid format syntax. DBCachePlugin does not provide a way to reference a topic preference. For that, perhaps you could use VarEXPAND macro.

Also, when I find myself needing SpreadSheetPlugin for one part of a formula and have multiple conditional statements, I sometimes find it easiest just to use CALC macro for the whole thing. Using your example above (which I'm not sure I completely understand), it might look something like this:

%CALC{"$IF($AND($IF($TIME(%EXPAND{"$percntdue$percnt" scope="%BASETOPIC%"}%) > $TIME(%DATE%)), $IF($TIME(%EXPAND{"$percntdue$percnt" scope="%BASETOPIC%"}%) <= $TIMEADD($TIME(),2,day))), A, )"}%

Looking back at your question, it appears you want to do this inside of a DBQUERY, so I'm not quite clear how this will fit. It doesn't require the DBQUERY. On the other hand, if you do want to put it inside a DBQUERY format, then you'd need to escape the CALC macro. You'd also escape the EXPAND macro and use $topic instead of %BASETOPIC%.

Finally, I'm trying to figure out if this is the same question as you asked in Question1280, for which I just added another possible solution.

-- LynnwoodBrown - 24 Apr 2013