0

I have this field named Expiry Date:-

enter image description here

now if using the UI calendar i added the date as 12 April 2023 >> then inside the API the date will be ExpiryDate: "2023-04-11T23:00:00Z", while if i added the list item using PowerShell as follow:-

Add-PnPListItem -List "ListData" -Values @{"ExpiryDate"=(Get-Date -Format "yyyy-MM-dd"); "Title"="test" ; "ty"=(Get-Date -Format "yyyy-MM-dd")}

and Today is 12 April 2023 , the data inside the REST API will be saved as follow:-

ExpiryDate: "2023-04-12T00:00:00Z",

so why we are getting this differences between adding the date using the UI or adding it using the Power shell?

1 Answer 1

1

Because when you add your date via the calendar UI, the date is converted from it's current time locale (site regional setting) to UTC. It stores that in the database as UTC. When you fetch the date from the API, the raw date value is returned as UTC and you need to convert it back to whatever time locale (UTC offset) that you require.

When you're adding the date via the API, the current time locale is not known and therefore SharePoint assumes the date being supplied is already UTC and therefore doesn't convert it before storing in the database.

12
  • ok thanks for your reply. so if i want to get the item which have expiry date = ExpiryDate eq '2023-04-12' will this item get returned "2023-04-11T23:00:00Z", or i need to write the query as ExpiryDate eq '2023-04-11'? Apr 12, 2023 at 11:30
  • 1
    If the date value you are using to filter by is not in UTC, then you could convert it and then filter by the UTC date. That way, you're accounting for the UTC offset in your date automatically. Apr 12, 2023 at 11:36
  • 1
    What's your locale set to? London is currently in British Summer Time (BST) which is UTC+01:00 Apr 12, 2023 at 11:47
  • 1
    What about the locale? You can find that in the regional settings. Apr 12, 2023 at 14:08
  • 1
    The UK is currently in BST (UTC+01:00). SharePoint takes this in to account as part of the locale setting, and therefore it converts the date chosen in the calendar UI from BST to UTC to be stored in the database (it removes one hour from whatever BST date you are choosing in the UI). Apr 12, 2023 at 14:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.