Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

Error: Sharepoint Lists Tool Dates Backdate or Change

Sethmb
Alteryx
Alteryx
Created

Environment Details


When using the Sharepoint List Input and Output Tools the Date and Date Time fields change when being pulled into Alteryx and pushed into Sharepoint. 
  • Alteryx Designer, Server
    • Version(s) All
  • Sharepoint
    • Version(s) 2013-2019, Online
  • Sharepoint List Input and Output Tools
 
No Error is thrown by this issue, the dates are sometimes back 1 day or forward 1 day. 
 
  • Date Only Variables will normally be backdated 1 day.
  • Date Time Variables will change based on the difference of the Sharepoint Site/Regional Timezone and UTC Time


Cause


This Sharepoint List backdating issue is typically seen when using Date Only Field types, and this is happening due to the way the Microsoft Sharepoint API is handling the Date and Date Time variables. When the Sharepoint API receives a Date Only field to add to the list, it adds a hidden timestamp which defaults to 00:00:00 or midnight for the conversion to UTC and then subtracts the difference to the Sharepoint Sites Time Zone. In this example, it is MDT so -6 hours. This then results in a date only field that is back one day. 

Additionally, if a date is entered into Sharepoint as a Date Time data type first, and then the field type is changed to Date Only, it will RETAIN the time stamp that was entered and the API will go off of this time. Which can result in some dates actually moving forward a day when being pulled into Alteryx. So if the Date Time was set to 5-12-2021 9:00PM, and then changed to date only. The Sharepoint API will still add +6 hours (MDT) to the 9:00PM which will then be 5-13-2021 3:00AM in Alteryx.

Example for MDT Sharepoint Site

In Alteryx, when pulling in a Sharepoint List with the Sharepoint List Input Tool that has a Date Only or Date and Time field, it is being called through the SharePoint API, and the fields will be changed to UTC time. When exporting from the Sharepoint List API it will +ADD the hour difference. It will then reverse the process and -SUBTRACT the difference in hours before being added into the SharePoint List. 
 

When using Date Only, there is a hidden timestamp on the backend that is being assessed for the conversion. This defaults to 00:00:00 or midnight. When pulling the list into other applications or Alteryx it will still show the correct date because it is adding hours to 00:00:00. For MDT this is +6 Hours to UTC, even though it is Date Only it will technically be 05-12-2021 06:00:00. However, if Date Only is being sent to the Sharepoint List, the Sharepoint API receives the Date Only and makes the Time Stamp 00:00:00 or Midnight again. This is the actual cause, where it subtracts time from 05-12-2021 00:00:00, and the resulting Date Only field in Sharepoint becomes 5-11-2021. The hidden Time Stamp is 5-11-2021 18:00:00. The hidden timestamp is temporary and does not stay with the Date Only variable. 


 


Resolution


 

  1. Because this is due to changes made by Microsoft Sharepoint API, to fix this in Alteryx the workflow will have to adjust the Date Only fields to send them back as Date and Time, and then the Sharepoint List will make them Date Only. 
  2. In the workflow, this can be done multiple ways, and it will be best to do this right before the Sharepoint List Output Tool to not interfere with the rest of the workflow. Add a formula tool and modify the field that contains the 'Date Only' variable.                                                               
  3. With a select tool, drop the original Date Only Field, and rename the Calculated Date Only field to the original so that it matches the Sharepoint List. 
Additional Notes

Again, even though the workflow is sending a Date and Time Field to the Date Only Sharepoint List Field, this is for the Sharepoint API to handle the conversions to and from UTC. The Sharepoint List Field being Date Only will then drop the time leaving the correct Date. 

When making the formula to modify the Time, instead of making the time change the exact difference between UTC and the Sharepoint Site Timezone, add a couple of hours to account for future day light savings. Example, MDT is +6 Hours, but later it will change to +7, so in the formula adding +8 or +9 hours instead will prevent issues with Day Light Savings. 
 
 
No ratings