Convert SharePoint date to serial using XSL

  • : Function split() is deprecated in /hsphere/local/home/guruj/guruj.net/modules/filter/filter.module on line 1200.
  • : Function split() is deprecated in /hsphere/local/home/guruj/guruj.net/modules/filter/filter.module on line 1200.
  • : Function split() is deprecated in /hsphere/local/home/guruj/guruj.net/modules/filter/filter.module on line 1200.
  • : Function split() is deprecated in /hsphere/local/home/guruj/guruj.net/modules/filter/filter.module on line 1200.

Another day, another SharePoint limitation.

Since you can't use Lookup fields in calculations, if we are trying to compare a Date being used as a Lookup key for a view then we have to resort to XSL. The following template will convert a SharePoint date to a serial number (UPDATE: oops -- made a beginner XSLT mistake and was assigning to variables, not parameters. The revised code is below. UPDATE 2: For some reason, certain select statements using the floor() function made value-of statement static. A better nested version below fixes this bug too.)

<xsl:template name="dateToSerial">
  <xsl:param name="date" />
  <xsl:comment>Returns a serialised version of a SharePoint date.  
	 XSLT algorithm taken from http://xsltcookbook2.atw.hu/xsltckbk2-chp-4-sect-4.html.
	 Value adjusted to match MS offset of January 1, 1900.
	 Note that this does not implement the Excel 1900 date bug so dates in 1900 may not be equal.
  </xsl:comment>
  <xsl:apply-templates select="." mode="dateToSerialCalc">
	<xsl:with-param name="year" select="ddwrt:FormatDateTime(string($date),1033,'yyyy')" />
	<xsl:with-param name="month" select="ddwrt:FormatDateTime(string($date),1033,'MM')" />
	<xsl:with-param name="day" select="ddwrt:FormatDateTime(string($date),1033,'dd')" />
  </xsl:apply-templates>
</xsl:template>
<xsl:template match="node()" mode="dateToSerialCalc">
  <xsl:param name="year" />
  <xsl:param name="month" />
  <xsl:param name="day" />
  <xsl:apply-templates select="." mode="dateToSerialCalc_S2">
	<xsl:with-param name="y" select="$year + 4800 - floor((14 - $month) div 12)"/>
	<xsl:with-param name="m" select="$month + 12 * floor((14 - $month) div 12) - 3"/>
	<xsl:with-param name="d" select="$day"/>
  </xsl:apply-templates>
</xsl:template>
<xsl:template match="node()" mode="dateToSerialCalc_S2">
  <xsl:param name="y"/>
  <xsl:param name="m"/>    
  <xsl:param name="d"/>    
  <xsl:value-of select="$d + floor((153 * $m + 2) div 5) 
      + $y * 365 + floor($y div 4) - floor($y div 100) 
      + floor($y div 400) - 2415019 - 32075 + 30"/>
</xsl:template>

The calling string then looks like this:

   <xsl:variable name="RealReportDate" 
    select="msxsl:node-set(substring-after(@Report_x0020_Date.,';#'))" />
   <xsl:apply-templates select="$RealReportDate" 
    mode="dateToSerial" />