REST api returns incorrect date for one DateTime column but the correct date for another DateTime column in the same list

Question

We have a list with a few DateTime(Date only is set) columns which are created through Powershell and there's no difference between them except the name. On a custom search page REST api is used to retrieve the items from the list but for some dates the api returns the previous day. For example if the correct date is 07/07/2023T00:00:00Z(that is the time in the database), the api returns 07/06/2023T23:00:00Z. At the first moment we thought the time zone is not properly set. We changed the server time zone to be UTC. We did the same with the Regional settings for the site collection. DB and FE server is one and the same so no difference there. The most strange thing is that not all dates are incorrect and for one list item the first DateTime column shows the correct date but the second one is incorrect. In the Database all dates are stored with time 00:00:00 because the fields are Date only. The problem is in the REST api which returns wrong dates. This is the api result:

That is the result from a CAML query made through CAML builder for the same dates:

That is a result for a few items - some of them are correct and some of them are not:

We don't have any other idea what could be the problem and any help will be appreciated!

Answer 1

We encountered similar “strange” results with an external application using search queries to retrieve document information. Your intuition about “UTC” was correct. When a user picks a date and time, SharePoint saves it in UTC. When displayed, the system adjusts the time according to regional settings. The main distinction is that, unlike the consistent global date, the time part in the DateTime columns changes with different time zones.

For every column containing a date (timestamp) value, a second column is created with the same name but with a “.” appended to it. The second column stores the UTC value, while the first one stores the “ConvertedFromUTCtoLocalTimeZone” value. For example:

['ColumnName']  = LocalTime.Value

['ColumnName.'] = UtcTime.value

For more details, you can refer to the Microsoft documentation on Keyword Query Language (KQL) syntax.

Additionally, you might find the following resources helpful:

• How to work with SharePoint datetime localization/timezones: This article provides an in-depth look at how SharePoint handles date and time localization.
• SharePoint UTC to Local time: A community discussion on converting UTC to local time in SharePoint.

These resources should give you a comprehensive understanding of how SharePoint manages date and time values across different time zones.