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

November 8th, 2011 | Posted by Marijn in code | xsl

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.

You can follow any responses to this entry through the RSS 2.0 You can leave a response, or trackback.

Leave a Reply

%d bloggers like this: