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:
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.