syntax for date format within set analysis

[mellerbeck]

I've noticed with set analysis that if using a date it of course needs to match exactly if for example you are using an input box to set a variable if you type 08/05/2011 versus 8/5/2011 one will work and the other won't.

So what is the correct syntax for a date format within set analysis, or is there a better (different?) way?

Sum({$<Tran_date = {"$(vNewDate)"}>}

It tried

Sum({$<Tran_date = {"date($(vNewDate),'MM/DD/YYYY')"}>}

but that doesn't seem to work.

[mellerbeck · Accepted solution]

Gah, got it of course after posting although a review wouldn't hurt my feelings

Sum(

{$<

Tran_date = {"$(=date(vNewDate,'MM/DD/YYYY'))"}>}

[#3]

I tend to convert my dates into numbers, and do comparisons and set analysis that way. Set analyisis with dates has bit me more than once.

[johnpaul]

I use variables for both if possible:

Sum ({$<Tran_Date = {">=$(vDateStartLY)<=$(vDateEndLY)"}>}Invoice)

Part of the reason for this is that we support systems outside of the US as well as the US, so the date formats need to be considered.