XsltListViewWebPart Date Format using DDWRT

The new way to do a SharePoint 2010 DataViewWebPart (DVWP) is to use a XsltListViewWebPart (XLV).

The premise is still the same – using SharePoint Designer, you add a data source (eg. Shared Documents) – and then format the output and so forth.

There are a bunch of differences, I resisted using the XLV, and kept using the old-school DVWP – but have been switching over to use the ‘new way’.

Anyways, I’ve been having some troubles with the way that DATES are being formatted within the new XLV.

The basic XSLT to use (and works great else where) is this :

   <xsl:value-of select="ddwrt:FormatDateTime(string(@Modified), 3081, ‘dd-MMM-yyyy’)" />

This should then display dates in AUSTRALIAN (3081) format – where I’m located – not a standard U.S. Date.

BUT – I’m seeing the following

  • Date : 08/06/2011 – showing as 06-AUG-2011
  • Date : 30/05/2011 – showing as 30-MAY-2011    (correct)

It looks like the DDWRT format is changing dates “if it can” – into a U.S. format date.    (BUG !?)

Any dates with a “DAY” less than 12 is being flipped – 1st of a month becomes JANUARY, etc.

I tried a LOT of different syntax :

  • ddwrt:FormatDateTime(string(@Modified), 1033, ‘dd-MMM-yyyy’) – same problem
  • Format as “ISO” date – and then re-format as ‘dd-MMM-yyyy’ – same problem
  • Made sure the RAW date is correct – 07/06/2011 11:13 AM   [ <xsl:value-of select="@Modified" /> ]

*aaarrrggghhhh*

See the screenshot here (click to view)…>

image

After a few HOURS of fiddling – I am convinced that there’s a BUG with the SharePoint 2010 XsltListViewWebPart – when using the DDWRT FormatDateTime

Perhaps the product team only tested with the U.S. date format – for 1033….?

Solution

In a moment of ‘desperation’, I ended up creating my own ‘date format’ routine – and then I can have FULL control over the values being displayed.   

You could change this to suit your own needs – and then don’t need to use the DDWRT methods.

This basically does the following :

  • Input param of a date
  • Get the first part – which will be the DAY – before the /
  • Add a leading “0” if needed
  • Get the second part – which will be the MONTH – and set as the short date value (JAN, FEB, MAR, etc)
  • Get the third part – which will be the YEAR

It now looks like this – which is CORRECT :

image

Here’s the XSLT :

    <xsl:template name="formatAustralianDate">

    <xsl:param name="dateValue"/> <!– expects raw date : eg. 31/05/2011 3:22 PM –>

    <xsl:variable name="day" select="substring-before($dateValue,’/’)"/>

    <xsl:variable name="month" select="substring(substring-after($dateValue,’/’),1,2)"/>

    <xsl:variable name="year" select="substring(substring-after(substring-after($dateValue,’/’),’/’),1,4)"/>

   <xsl:choose>

   <xsl:when test="string-length($day)=1">0</xsl:when>

   </xsl:choose>

   <xsl:value-of select="$day" />-

   <xsl:choose>

   <xsl:when test="$month=’01’">Jan</xsl:when>

   <xsl:when test="$month=’02’">Feb</xsl:when>

   <xsl:when test="$month=’03’">Mar</xsl:when>

   <xsl:when test="$month=’04’">Apr</xsl:when>

   <xsl:when test="$month=’05’">May</xsl:when>

   <xsl:when test="$month=’06’">Jun</xsl:when>

   <xsl:when test="$month=’07’">Jul</xsl:when>

   <xsl:when test="$month=’08’">Aug</xsl:when>

   <xsl:when test="$month=’09’">Sep</xsl:when>

   <xsl:when test="$month=’10’">Oct</xsl:when>

   <xsl:when test="$month=’11’">Nov</xsl:when>

   <xsl:when test="$month=’12’">Dec</xsl:when>

   </xsl:choose>

   -<xsl:value-of select="$year" />

   </xsl:template>

And – here’s how to CALL to it – for the “Modified” field (default SharePoint column)…>

   <xsl:call-template name="formatAustralianDate">

   <xsl:with-param name="dateValue" select="@Modified" />

   </xsl:call-template>

—————————————

And – that fixed my problem.   I’m sure there HAS to be another way – and/or a bug-fix or patch – but I thought I’d share this solution – and hopefully, it will help YOU also.

…. and let me know if you find a FIX for this annoying problem !

Smile

Advertisements

20 thoughts on “XsltListViewWebPart Date Format using DDWRT

  1. Have you had any luck finding a fix? Have you found anyone else with the same issue?

    We have also found that the same issue appears when we try a straight date compare in conditional formatting (e.g. “DueDate greater than or equal to [CurrentDate]”). I’m wondering if it could be a server localisation configuration issue?

    I posted a question about this problem to EggHeadCafe and got a suggestion to to set the localisation setting of the page to en-gb:

    en-GB

    I didn’t see any difference but might be worth a look.

    Stephen

    BTW I’m the same Stephen Booth as the above comment, just got a WordPress account now.

    Like

    • Solution / Work around
      I ended up using an out of the box calculated column (A) to subtract PlannedDate from ActualDate.

      Calculated columns seem not to have the US date issues.

      If completed late it is a negative number.
      I then used another calculated column (B) and an If statement to return Yes if calculated column (A) was a negative number.
      I then set the conditional formatting on calculated column (B), such as if Yes then show an image (such as an exclamation mark image).
      Long winded work around but got there.

      Like

  2. Same problem here. I am also trying to compare two dates, like Stephen does, with no success so far. I am also dealing with Australian Time Format.

    Like

  3. I have used parts of your code in a date comparison. Hopefully this will save somebody some time:

    –0

    The date is in the past

    Like

  4. Pingback: Bug With SharePoint 2010 XSLT DateFormat Function | E. Struyf 's Blog

  5. Pingback: XSLT-сюрприз « Dkms's Blog on WordPress

  6. Arrrrggghhh… I am having the same issue as the users above with conditional formatting of EndDate greater than or equal to and nothing happens.

    I too am in Australia and have changed format of the date to but the conditional formatting will not apply!

    I admit, I haven’t tried the solution above because it surely doesn’t need to be that difficult!

    If anybody has found an “easy” solution, I would love to hear it.

    Like

  7. Kelly,

    I tried posting my code on this blog but it got wiped out, unfortunately. I can email you the code I used to compare the dates, hopefully it will help you. My email is aboehmer [at] addictivemedia com au.

    I will also email the author of this article with my code, perhaps he can put it up for others.

    Like

  8. Hi Andreas,

    Thanks for your reply. I did end up finding a solution that worked for me:-

    (number(ddwrt:FormatDateTime(ddwrt:FormatDate(string(@EndDate),1033,1),1033,’yyyyMMdd’)) <=
    number(ddwrt:FormatDateTime(ddwrt:FormatDate(string($Today),1033,1),1033,'yyyyMMdd')))

    The only thing now is trying to get the conditional formatting to format by the time – the above only works with date. I'm not a developer so if there's any solutions not too difficult that would be great.

    Like

  9. I’ve created a Web Part using Visual Studio to show the selected columns of a list in gridview. But the problem is that whenever I’m changing the locale to English-UK (it’s by default English-US), unfortunately it as well as the site has no effect on it though the date format is supposed to be changed.
    I think I’m facing the problem because of the custom webpart that contains a gridview of an existing list.
    I need your help!!!

    Like

  10. Hi all,

    Here’s something I did to display my Australian Time date for my @EventStartDate to display properly when it start doing the twist of day and month.
    Hope that this will help.. 🙂

    January
    February
    March
    April
    May
    June
    July
    August
    September
    October
    November
    December

    Like

  11. Be careful with this!

    I’ve just been tackling this same issue and it appears that the XSLT List View Web Part presents dates to your XSLT based on the current user’s regional settings, which aren’t always the same as the regional settings of the Web.

    So your fix might work for users who have English(Australia) has their regional settings, but in a global environment you can’t rely on users all sticking with the same regional settings, and you may in fact be swapping around the day and month when you don’t need to!

    Check this link out for officially supported workarounds:
    http://support.microsoft.com/kb/2580994

    Like

  12. Ahaa, its good discussion regarding this piece of writing at this place at this website, I have read all that, so now me
    also commenting at this place.

    Like

  13. Hi
    My servers and users are in the UK.
    I’ve had the same problem where a day less than 12 seems to flip and get the wrong conditional formatting.

    I need to format a row if Expiry date < today ?

    Will Chris's routine work fir this?

    Thanks

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s