cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Nintex Newbie

Query List does not return any results on a DateTime field filter

Jump to solution

Why does Query List not return any results on a DateTime field filter?  I am using greater than or equal to, and less than or equal to, contains, and begins with, and none of them work.  I know the data that should be returned, but it is not being returned.  What can I do to make this work?

Labels: (1)
Tags (1)
0 Kudos
Reply
3 Replies
Highlighted
Nintex Newbie

Re: Query List does not return any results on a DateTime field filter

Jump to solution

The answer to this as I found out is that Date/Time fields are filtered in the Query List action different than how they appear in the list view and even how they are queried without a filter in the Query List action.  For example, this is how it turned out in my case:

 

In the List view All Items (default view), looking at one row, the date/time field ConfirmedMoveDate appears like this: 08/19/2015

When I query the whole collection of ConfirmedMoveDate values with Query List (no filter) and store in a collection, they appear like this: 08/19/2015 12:00:00 AM; 01/15/2015 06:00:00 PM; 02/06/2014 12:00:00 AM; etc. etc.

When I query the collection of ConfirmedMoveDate with Query List and use a filter, I need to format the date in this way: YYYY-MM-DDT00:HH:MMZ (for example: 2015-08-19T00:12:00Z or 2015-01-15T00:18:00Z, etc.).  00:18:00 is military time and means 6:00 PM.

If I do not format the date in this way, the Query List action produces no results, and there is no indication that it needs the TZ format.   So, it is very confusing if you don't know you need that format.

 

So, I built 2 workflow text variables in Build String actions and used them in Query List to filter the ConfirmedMoveDate values in a month range.  The filter is set up like this:

Where txtStartMonthRange is Greater than or Equal To {WorkflowVariable:YearInput}-{WorkflowVariable:MonthInput}-01T00:00:00Z And

Where txtEndMonthRange is Less than or Equal To {WorkflowVariable:YearInput}-{WorkflowVariable:MonthInput}-31T00:23:59Z

Collect IDs in collection variable colConfirmedMoveIDs.

 

It works!

View solution in original post

0 Kudos
Reply
Highlighted
Nintex Newbie

Re: Query List does not return any results on a DateTime field filter

Jump to solution

I should correct one bit of information.  The 2 text variables are first set up in Build String actions as:

txtStartMonthRange: {WorkflowVariable:YearInput}-{WorkflowVariable:MonthInput}-01T00:00:00Z and txtEndMonthRange: {WorkflowVariable:YearInput}-{WorkflowVariable:MonthInput}-31T00:23:59Z

Then the Query List filter is set up like this:

Where ConfirmedMoveDate field is Greater than or Equal to txtStartMonthRange And

Where ConfirmedMoveDate field is Less than or Equal to txtEndMonthRange

0 Kudos
Reply
Highlighted
Not applicable

Re: Query List does not return any results on a DateTime field filter

Jump to solution

Thanks for letting us know you figured it out!

0 Kudos
Reply