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?
Query List does not return any results on a DateTime field filter
+3+3The 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!
+3I 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
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.