How can I compare dates within a format string in dbquery?

How can I compare dates within a format string in dbquery?

format="| $formfield(due) | $percntIF{\"d2n('$formfield(due)') < d2n('19 Apr 2024')\" then=\"A\"}$percnt | "

I try to compare two time formats, whereas one is stored in the formfield named ""due". Alas it returns always the result A regardless, if the value for due is earlier or later than 19 Apr 2024. It seems that d2n('$formfield(due) is always zero. due is in the format 22 Apr 2013.

Any idea? Thanks in advance!

-- ThomasSchmidt - 22 Apr 2013

Does

format="| $formfield(due) | $percntIF{\"d2n('$topic'/due) < d2n('22 Apr 2013')\" then=\"A\" else=\"B\"}$percnt | " 

make a difference? This type of d2n construct seems to be used quite often in applications that use dates on Foswiki.org

And there are some d2n related examples at WriteAQueryCookbook you could try.

-- FranzJosefGigler - 22 Apr 2013

Thx, but no. For me it seems that d2n simply doesn't allow variables. Whatever variable I insert in d2n there is no result.

-- ThomasSchmidt - 23 Apr 2013

ok, just found out that you don't have to use d2n in the first place. That means it works just using the variable instead of d2n

-- ThomasSchmidt - 23 Apr 2013

I believe this should work:

format="| $formfield(due) | $percntIF{\"$IF($TIME($formfield(due)) < $TIME(%DATE%), A, B)\"}$percnt | " 

-- LynnwoodBrown - 24 Apr 2013

No sorry nothing worlks within DBQUERY. Perhaps there is a bug in that plugin

$percntIF{\"$percntCALC{$IF($TIME($formfield(due)) < $TIME(25 Apr 2013), A, B)\"}$percnt}$percnt

doesnt work either

-- ThomasSchmidt - 25 Apr 2013

DBQUERY will convert $d2n(dateformfiled) to epoch seconds. So the example should be something along the lines of:

%DBQUERY{
   "query"
  format="| $formfield(due) | $percntIF{\"$d2n(due) < $d2n('22 Apr 2013')\" then=\"A\" else=\"B\"}$percnt | " 
}%

-- MichaelDaum - 27 May 2013

Thanks!

-- ThomasSchmidt - 28 May 2013

Commenting is disabled while not logged in