Loading dates via SharePoint Designer Datasource creates problems with daylight savings time

At a dashboard project I was using the SharePoint Designer DataSource functionality, getting data from a SQL-Server (more on why in this blogpost).

I was having a funny issue however: all dates I got from SQL-SERVER had 1 hour off. When I had the date “15/10/2011” in SQL, I would have “14/10/2011 23:00:00Z”. Sidenote: in Excel, the value was correct.

I looked at the code behind the page and found this value-of-select:

<xsl:value-of select="@DATE_IN"/>

I double-checked the regional settings on SQL, SharePoint, site and my pc, but all were ok.I posted the question on the MSDN forum (great source if you can’t find a solution to your SharePoint issues) and AbedKhooli replied me that those dates are put in UTC because of the xml standard. I needed to convert them one by one.Final code is as follows:

<xsl:value-of select="ddwrt:FormatDateTime(string(@DATE_IN),2067,'dd/MM/yyyy')" />

In this example, “2067” is my locale and ‘dd/MM:yyyy’ is how my date is supposed to show up.Works perfectly.

About: Marijn

Marijn Somers (MVP) has over 14 years experience in the SharePoint world, starting out with SP2007. Over the years the focus has grown to Office 365, with a focus on collaboration and document management. He is a business consultant at Balestra and Principal Content Provider for "Mijn 365 Coach" that offers dutch employee video training. His main work tracks are around user adoption, training and coaching and governance. He is also not afraid to dig deeper in the technicalities with PowerShell, adaptive cards or custom formatting in lists and libraries. You can listen to him on the biweekly "Office 365 Distilled" podcast.