cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

How do I perform a query list search to retrieve records from inbetween DATE/TIME X to DATE/TIME Y

When I perform an "Action - Query list" to gather items from a list and then filter on Start Date for between two DATE/TIMES, the query ignores the time stamp and provides items from the entire date from 12:00AM - 11:59PM.  I can't seem to distinguish items based on the full DATE & TIMESTAMP, though that information is there in the field and is reporting.

 

 

For reference (calculated variables - both DATE/TIME variables defaulted to Blank)

 

This workflow runs weekdays at 7am.

Workflow has History log in several spots and all variables for DATE/TIME are returning the DATE & TIME correctly throughout the entire workflow process.

 

Thanks for the time!  This has been stumping me for a while.

Labels: (1)
Reply
2 Replies
Not applicable

Re: How do I perform a query list search to retrieve records from inbetween DATE/TIME X to DATE/TIME Y

Nintex support responded to my issue.   I needed to utilize a combination of relying on UTC time codes, storing the data in ISO 8601 multi-line strings and switching my Action - Query list from query builder to CAML code editor to add some extra text.

 

See my notes.

 

Nintex Support supplied this article : http://sharepointtaproom.com/2012/10/26/caml-query-utc-date-comparisons-in-sharepoint/

 

I had to perform the following steps:

 

  1. Duplicate(or change) my calculated date used DATE TIME variables into Multiline string variables
  2. Change my Calculated dates used in the Query List – action filters to store in ISO 8601 date string
  3. Change my Query List actions from Query Builder to CAML Editor
  4. Modify the selections for:

 

     <Where>  
     <And>
  
     <And>
      
     <Gt>
        
     <FieldRef Name="StartDate"  />
      
     <Value Type="DateTime”>{WorkflowVariable:dateYesterdayAT7amISO}</Value>
   
     </Gt>
      
     <Lt>
        
     <FieldRef Name="StartDate"  />
        
     <Value Type="DateTime”>{WorkflowVariable:dateCurrentDateAT7amISO}</Value>
 
     </Lt>
    
     </And>

     </Where>

 

To include the following info addition:  IncludeTimeValue=”TRUE” StorageTZ=”TRUE”

 

End Result:

 

<Where>  
<And>
    
<And>
      
<Gt>
        
<FieldRef Name="StartDate"  />
        
<Value Type="DateTime" IncludeTimeValue="TRUE" StorageTZ="TRUE">{WorkflowVariable:dateYesterdayAT7amISO}</Value>
      
</Gt>
      
<Lt>
        
<FieldRef Name="StartDate"  />
        
<Value Type="DateTime" IncludeTimeValue="TRUE" StorageTZ="TRUE">{WorkflowVariable:dateCurrentDateAT7amISO}</Value>
      
</Lt>
    
</And>

</Where>

Reply
Workflow Hero

Re: How do I perform a query list search to retrieve records from inbetween DATE/TIME X to DATE/TIME Y

I've been struggling with this issue for the last 2 hours.  This is exactly the answer I needed.  Thank you. 

0 Kudos
Reply