Performing Date / Time Calculation in DVWP Using XSL

SharePoint Data View Web Parts (DVWP) may be leveraged to use complex conditional logic using XSL.  Such an example would be to highlight List Items in the DVWP that meet certain date/time criteria.  The following example will show how XSL may be leverage as a simple and robust solution.

Case:

New tasks (i.e. List Items created within the last 30 minutes) in a User’s “My Tasks” DVWP are highlighted to bring attention to these high priority items.

Undesirable Solution:

Use a declarative workflow to check if the task is no longer new and reset a new ”flag”.  XSL will check the “flag” when the DVWP renders and display tasks appropriately.

The workflow would follow the logic:

  1. Start on Item creation
  2. Pause for 30 minutes
  3. Set hidden boolean field value (isNew) to false

The DVWP then uses XSL to check the value of isNew to set the CSS class of a TR, TD, or some other HTML DOM element:

<xsl:if test=”@isNew = 1″><xsl:attribute name=”class”>newTask</xsl:attribute></xsl:if>

This solution is not optimal as a long-term workflow runs on every List Item.  As a result, numerous workflows may be running on the server causing decreased server performance.

Solution:

Use XSL to check if a Task is new and set the CSS class appropriately when the DVWP renders.

Logic

For simplicity of the XPath expression, a task may be considered new if both of these conditions are true:

  1. The date of creation is the current day (i.e. today)
  2. The time of creation occurs less than 30 minutes before the current time (i.e. now)

(This logic will not handle Tasks assigned from 23:41 to 23:59 as new after 00:00 of the next day.  Such Tasks may be handled with a seperate XPath expression to properly determine their new condition.)

Comparing the Date

Checking that the date of creation is today requires a comparison of equality between two dates.  Therefore, it is possible to use the out-of-the-box (OOTB) ”equal to” operator; unlike the “equal to” and “not equal” to operators, other operators (e.g. “numerical addition”, ”less than”, etc.) require numerical arguments.  Created must be formatted properly as it is stored in the International Organization for Standardization (ISO) ISO8601 format (YYYY-MM-DDTHH:MM:SSZ) and therefore contains data other than the date.  The method FormatDateTime() (see MSDN SharePoint Developer Center) requires the date as a textual argument and thus Created must be converted to a string using the method string().  For accuracy, the method FormatDateTime() is used as it allows for the explicit definition of date/time formatting.  In this example, the format “MM/dd/yyyy” is used.  For accuracy, today’s date (i.e. ddwrt:Today()) must undergo the same formatting procedure.

The XPath expression to compare the dates is:

ddwrt:FormatDateTime(string(@Created),1033,”MM/dd/yyyy”) = ddwrt:FormatDateTime(string(ddwrt:Today()),1033,”MM/dd/yyyy”)

Comparing the Time

The time of creation from Created may be retrieved by formatting the ISO8601 value using the method FormatDateTime() and a date/time format of “hhmmss”.  The format “hhmmss” is used so that each component of time (i.e. hour, minute, second) may be easily retrieved individually.  Each component may be retrieved using these XPath expressions:

hour: substring(string(ddwrt:FormatDateTime(string(@Created),1033,”hhmmss”)),2,2)
minute: substring(string(ddwrt:FormatDateTime(string(@Created),1033,”hhmmss”)),4,2)
second: substring(string(ddwrt:FormatDateTime(string(@Created),1033,”hhmmss”)),6)

Each component of time will be converted to the basic time unit.  In this example, the basic time unit is minutes (as the duration of the new condition is “30 minutes”).  Each component converted to minutes may be retrieved with these XPath expressions:

hour: substring(string(ddwrt:FormatDateTime(string(@Created),1033,”hhmmss”)),2,2) * 60
minute: substring(string(ddwrt:FormatDateTime(string(@Created),1033,”hhmmss”)),4,2)
second: substring(string(ddwrt:FormatDateTime(string(@Created),1033,”hhmmss”)),6) div 60

Adding each component converted to minutes will provide the time as minutes since 00:00 of that day.  This “minute-time” new may be calculated using the XPath expression:

(substring(string(ddwrt:FormatDateTime(string(@Created),1033,”hhmmss”)),2,2) * 60) + substring(string(ddwrt:FormatDateTime(string(@Created),1033,”hhmmss”)),4,2) + (substring(string(ddwrt:FormatDateTime(string(@Created),1033,”hhmmss”)),6) div 60)

As tasks are new if created within the past 30 minutes (i.e. the difference between the time of creation and current time is less that 30 minutes), a task may be determined to be new using the XPath expression:

((substring(string(ddwrt:FormatDateTime(string(ddwrt:TodayIso()),1033,”hhmmss”)),2,2) * 60) + substring(string(ddwrt:FormatDateTime(string(ddwrt:TodayIso()),1033,”hhmmss”)),4,2) + (substring(string(ddwrt:FormatDateTime(string(ddwrt:TodayIso()),1033,”hhmmss”)),6) div 60)) - ((substring(string(ddwrt:FormatDateTime(string(@Created),1033,”hhmmss”)),2,2) * 60) + substring(string(ddwrt:FormatDateTime(string(@Created),1033,”hhmmss”)),4,2) + (substring(string(ddwrt:FormatDateTime(string(@Created),1033,”hhmmss”)),6) div 60)) < 30

The XPath expression may be abstracted as: (the current time in minutes) – (the created time in minutes) < 30 minutes.

Durations Greater than 24 hours

If a duration to check surpasses 24 hours, calculation must be performed on the date values.  As no OOTB date/time comparison operators exist, the dates must be converted to numbers.  Dates may be converted to numbers using the format “yyyyMMdd”; this format is acceptable for numeric comparison as dates inherently have hiearchical components; years are superior to months which are superior to days (e.g. any date in 2009 is newer than any date in 2008 and any day in December is newer than any day in November of the same year).  The difference between two dates (in days) may be calculated with the XPath expression:

number(ddwrt:FormatDateTime(string(ddwrt:TodayIso()),1033,”yyyyMMdd”)) – number(ddwrt:FormatDateTime(string(@Created),1033,”yyyyMMdd”))

Conclusion

Generally, XSL enables powerful conditional rendering of DVWP that may greatly enhance the basic functionality found with OOTB DVWP.  The provided example illustrates how XSL and XPath expressions may be used to perform logical calculations and comparisons using basic numerical operators.  This capability may be used to benefit server performance and reduce SharePoint “clutter” by avoiding unecessary columns.  The use of XSL should be considered but is not appropriate for all scenarios (e.g. some data is best stored in a column rather than dynamically calculated).

UPDATED: 17 NOV 2009

The method TodayIso() (see MSDN SharePoint Developer Center) is incorrectly specified to return the current date and time in ISO8601 (see World Wide Web Consortium and Wikipedia).  The returned format is not ISO8601 as the value is terminated by the character “Z” (i.e. Zulu) incorrectly denoting the time is in UTC.  The returned time is in the server’s local time zone.  Therefore, a server hosted in the Eastern Time Zone (i.e. UTC-5) may return the value “2010-01-01T00:00:00Z” rather than the appropriate value ”2010-01-01T05:00:00Z”.  Therefore, this example assumes that any servers are set to UTC.

UPDATED: 30 MAY 2013

The proposed method of finding the difference between two dates (last section: “Durations Greater than 24 hours”) works only for dates within the exact same month; it does not provide accurate results if the two dates are within different months or years.

(H/T to James for identifying this oversight)

Technology, Tips & Tricks, , , , , .

12 Comments

  1. PBPB
    June 23, 2010 at 3:55 pm

    Thank you! thank you! thank you! You are a life saver

  2. zzz
    August 5, 2010 at 4:53 pm

    That all worked well enough but for me the indices of time was wrong. Instead of 2,2; 4,2; and 6 I used 1,2; 3,2; and 5 and the calculation was correct.

    • July 5, 2011 at 12:42 pm

      Thanks for the feedback. I’ll try to re-examine the snippets for accuracy (and maybe some improvements)!

  3. October 22, 2010 at 10:29 am

    Hi,

    Your example works perfectly for @Created metadat.
    Unfortunately, it doesn’t work for calculated column which return date/time type…

    string(@CalculatedColumnDateTime) returns blank string.

    Do you have a solution for this case?

    Thanks for your help!

    Regards.
    François

    • July 5, 2011 at 12:41 pm

      Francois:
      My apologies for the late response! Please see suribabu’s comment thread for my full response.

  4. suribabu
    June 22, 2011 at 12:12 pm

    Your example works perfectly for @Created metadat.
    Unfortunately, it doesn’t work for calculated column which return date/time type…

    string(@CalculatedColumnDateTime) returns blank string.

    Do you have a solution for this case?

    Thanks for your help!

    • July 5, 2011 at 12:39 pm

      Suribabu (and Francois):
      I was able to use a Calculated Column (Date/Time) without issue. Have you been able to confirm that the column is (1) configured for Date/Time, (2) not empty, and (3) is included in the DVWP’s data source?

  5. AJ
    December 21, 2011 at 5:16 pm

    Thank you Thank you Thank you!

    I can confirm with Christopher H. Lincoln that this does work for calculated columns!

    Appreciate your post, saved me a lot of time figuring this out.

  6. Troy
    September 18, 2012 at 4:18 am

    Hi,

    Im trying to create a filter on a dataview that brings back birthdays for the current month and for the current week. How would you do this.

    Regards

    Troy

    • Christopher Lincoln
      September 19, 2012 at 6:20 pm

      Troy-

      To display only items that occur this month, you could use a comparison like:
      “ddwrt:FormatDateTime(string(@Birthday),1033,”MM”) = ddwrt:FormatDateTime(string(ddwrt:Today()),1033,”MM”)”

      Determining if something occurs this week is much more complex. Once the items are filtered to only display items from this month, I would use something like “ddwrt:FormatDateTime(string(ddwrt:Today()), 1033, “ddd”)” to get the current day in a format like “Wed” (or “dddd” for “Wednesday”). Now that the day numeric value has been converted to the day of the week and assuming Sunday is the first day of the week, we can perform some basic comparisons to target items that have a “Birthday” field value greater than or equal to Sunday (i.e. three days ago; Wednesday – 3) and less than or equal to Saturday (i.e. three days from now; Wednesday + 3). Note that it might make more sense to “filter out” rather than “filter in” by targeting items that don’t occur this week (see the example below, which targets items that do not occur this week to apply in-line CSS).

      Since such logic would benefit from “if/else” or “switch” logic, I’d recommend using the xsl:choose element to segment your filter like so:
      <xsl:template name=”dvt_1.rowview”>
      <tr>
      <xsl:if test=”[item occurs this month]“>
      <xsl:choose>
       <xsl:when test=”[today is Sunday]“>
        <xsl:if test=”[item does not occur this week]“>
        <xsl:attribute name=”style”>display:none;</xsl:attribute>
        </xsl:if>
       </xsl:when>
       <xsl:when test=”[today is Monday]“>
        <xsl:if test=”[item does not occur this week]“>
        <xsl:attribute name=”style”>display:none;</xsl:attribute>
        </xsl:if>
       </xsl:when>
       <xsl:when test=”[today is Tuesday]“>
        <xsl:if test=”[item does not occur this week]“>
        <xsl:attribute name=”style”>display:none;</xsl:attribute>
        </xsl:if>
       </xsl:when>

       <xsl:when test=”[today is Saturday]“>
        <xsl:if test=”[item does not occur this week]“>
        <xsl:attribute name=”style”>display:none;</xsl:attribute>
        </xsl:if>
       </xsl:when>
       <xsl:otherwise>
        <xsl:attribute name=”style”>display:none;</xsl:attribute>
       </xsl:otherwise>
      </xsl:choose>
      <td class=”ms-vb”><xsl:value-of select=”@Title” /></td>
      </xsl:if>
      </tr>
      </xsl:template>

      It’s not pretty, but it should work.

  7. James
    May 13, 2013 at 11:07 pm

    To calculate the “difference between two dates (in days)” you suggest converting the two dates to yyyyMMdd format and subtracting one date from the other.

    20130501 – 20121201 (5 months, approx 150 days). Numeric subtraction gives 9300 days. This doesn’t work. or did I miss something obvious?

    • Christopher Lincoln
      May 30, 2013 at 5:06 pm

      James-

      Excellent catch! As you mention, the proposed method of finding the difference between two dates would provide incorrect results if the dates are not within the same month and year (i.e. the method only works if the two dates are within the exact same month).

      Unfortunately, I’m unable to think of a precise solution; though something along the lines of JavaScript’s Date.getTime() method (which converts date/time values into milliseconds since 00:00 01 JAN 1970) seems plausible. I’ll keep this in the back of my mind and post an update if I come up with anything!

Your email is kept private. Required fields are marked *

*
*