topic Re: Wrong dates when loading Sharepoint list data to Qlik Sense in Connectivity & Data Prep

Wrong dates when loading Sharepoint list data to Qlik Sense

Archie — Sun, 17 Oct 2021 09:04:10 GMT

Hi,

I'm experiencing quite an odd issue, where some of the date fields loaded from a Sharepoint list, via Qlik O356 Sharepoint Connector appear to be one day behind of the actual date. So for instance if a list item has 9.6.2021 ('D.M.YYYY') as it's start date in a Sharepoint list, after loading the data to Qlik, the date is some how shown as '2021-06-08T21:00:00Z' and after date(floor()) formatting 8.6.2021. However, for some list items, the date is shown correctly, but I have several instances where the issue occures.

Has anyone experienced similar issues and possibly find a solution for it?

I'm using Web Connector version December-2020 and Qlik Sense Enterprise version September-2020 Patch 5.

Re: Wrong dates when loading Sharepoint list data to Qlik Sense

rubenmarin — Sun, 17 Oct 2021 09:32:45 GMT

Hi, it looks like when you see in sharepoint you are looking the local time, but when Qlik recovers it it's in UTC time.

If this is what is happening you can add your local utc hours before convert it to date, so if local time is UTC+4 you can do Date(Floor(DateField+(4/24))) as Date // adding 4 hours to convert to local date.

Re: Wrong dates when loading Sharepoint list data to Qlik Sense

Archie — Sun, 17 Oct 2021 09:59:57 GMT

Superb, that solved the problem. Many thanks!