Query against a date range.

[Jeff Myers]

I am trying to query against a date range and am having difficulty with the syntax.

I would like to be able to query for Features that have a planned end date between 30 and 60 days from today but the information I am seeing on your help site don't seem to address this.
https://docs.ca.com/en-us/ca-agile-central/saas/use-grid-app-queries#dates

I see how I can get a day, week, month, or quarter but I don't see how to get 30 and 60 days.

I was trying this but it didn't work;
((PlannedEndDate >= "today+30") AND ((PlannedEndDate <= "today+60")

any help would be appreciated.

Date Variables

When writing a query against a date field-such as the end of an iteration-you can use variables to automatically update your app as time progresses. There are many variables available:

Variable	Meaning	Timezone associated with your...	Length
Today	current date	user account	1 day
Yesterday	the day before today	workspace	1 day
Tomorrow	the day after today	workspace	1 day
LastWeek	7 days before today	workspace	through the end of today
NextWeek	7 days after today	workspace	from the start of today
LastMonth	30 days before today	workspace	through the end of today
NextMonth	30 days after today	workspace	from the start of today to...
LastQuarter	90 days before today	workspace	through the end of today
NextQuarter	90 days today	workspace	from the start of today to...
LastYear	365 days before today	workspace	through the end of today
NextYear	365 days after today	workspace	from the start of the day to...

------------------------------
Jeff Myers
Agile Transformation Coach
Unify Consulting
Seattle WA
------------------------------

[David LeDeaux]

Hi Jeff

Typing this on my mobile so I can't test this but I do see an issue with the parentheses in your query

Try this:

((PlannedEndDate >= "today+30") AND (PlannedEndDate <= "today+60"))​

[Jeff Myers]

David, thank you for the quick response,

I adjusted the parenthesis as suggested and still got the same results.
((PlannedEndDate <= "today+60") AND (PlannedEndDate >= "today+30"))

I also reversed the order and there was no change in the results.
((PlannedEndDate <= "today+60") AND (PlannedEndDate >= "today+30"))

Obviously 6/20 does not fall with in the parameters I am trying to set.

Am I missing something?

Jeff

------------------------------
Jeff Myers
Agile Transformation Coach
Unify Consulting
Seattle WA
------------------------------

[Jeff Myers]

David,

Thank you for the quick response.

 

I did what you suggested ;

((PlannedEndDate >= "today+30") AND (PlannedEndDate <= "today+60"))

 

And this is the result;

 

It does not seem to be respecting the first parameter?  This is the behavior I was seeing before I sent the e-mail.  What am I missing, it seems like it should work?

 

Jeff

 

[David LeDeaux]

There's something strange going on there.  I played with it on my end and I couldn't get a >= operator to work when using +days.  

What I did get to work was only using a > operator.  Of course you have to adjust the days but I think this would be mathematically equivelant:

((PlannedEndDate > "today+29") AND (PlannedEndDate <= "today+60"))​

I'm going to ping the dev team on this and see what their thoughts are

[Sagi Gabay]

Tried it as well.

David, you're correct.

The > operator seems to work, while >= doesn't seem to.

Also, it seems that this query works:
((PlannedEndDate > "today+29") AND (PlannedEndDate <= "today+60"))

While this one doesn't:
((PlannedEndDate > "today+29") AND (PlannedEndDate < "today+61"))

So, it seems like also the combinations are impacted. You may want to have Development check it more wholly.

Sagi

[David LeDeaux]

The response I got back is >= "Today+30" is a range, so seems that may not work.  I haven't had time to digest that response yet.  They concur that the correct solution is to use the > operator.