This question about : Asked

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

 

QuestionForm edit

Subject
Extension DBCachePlugin
Version Foswiki 1.1.8
Status Asked
Related Topics
Topic revision: r2 - 24 Apr 2013, LynnwoodBrown
The copyright of the content on this website is held by the contributing authors, except where stated elsewhere. See Copyright Statement. Creative Commons License    Legal Imprint    Privacy Policy