Lately we found a very interesting fact of Sharepoint, which I think is worth writing a post about. Although it is very rare that someone will read directly into Sharepoint database to retrieve information, but for some hardcore users, especially with my last post regarding how Sharepoint stores data in backend database, one can easily read into Sharepoint Prod_Content database and do CRUD transactions.
However, there is a big alert to notice that Sharepoint stores all date field in UTC! That is, if you are saving a datetime field in a list, Sharepoint actually converts the time that you selected into UTC, and converts it back to whatever time zone the person is in when retrieving. This happened when we realised the number of records shown in Sharepoint’s list doesn’t match the number of records shown in Crystal report, which runs SQL query directly to Sharepoint database.
One workaround is to use date only field as much as possible, and avoiding the time. But if time is needed and your other business applications requires reading directly into Sharepoint database, then better mechanism is required. There is a built in function in Sharepoint database called, fn_LocalDayFromUTCDate which converts UTC time back to local datetime (i.e. the user’s timezone).