Issue using ddwrt:FormatDateTime in xsl variable

Question

I have a data view and am trying to filter using 2 date filter web parts. I'm assigning the date filter values to parameters ($StartDateFilter and $EndDateFilter). I'm struggling with the filter data view filter condition.

I have tried the following:

<xsl:variable name="ConvertedStartDateFilter" select="number(ddwrt:FormatDateTime(string($StartDateFilter), 2057, 'yyyyMMdd'))" />
<xsl:variable name="ConvertedEndDateFilter" select="number(ddwrt:FormatDateTime(string($EndDateFilter), 2057, 'yyyyMMdd'))" />      
<!-- Filter using @StartDate 'translate' -->
<xsl:variable name="Rows" select="/dsQueryResponse/Work/Rows/Row[number(translate(@StartDate,'/','')) &gt;= $ConvertedStartDateFilter and number(translate(@StartDate,'/','')) &lt;= $ConvertedEndDateFilter]"/>        

The problem is that @StartDate is translated into yyyyddMM which obviosly gives the wrong results. I need it to be in yyyyMMdd.

So I have tried replacing the last line above with:

<!-- Filter using 'FormatDateTime' -->
<xsl:variable name="Rows" select="/dsQueryResponse/Work/Rows/Row[number(ddwrt:FormatDateTime(string(@StartDate), 2057, 'yyyyMMdd')) &gt;= $ConvertedStartDateFilter and number(ddwrt:FormatDateTime(string(@StartDate), 2057, 'yyyyMMdd')) &lt;= $ConvertedEndDateFilter]"/>

but this causes the page to throw the generic 'Unable to display this Web Part' error.

I know that the statement number(ddwrt:FormatDateTime(string(@StartDate), 2057, 'yyyyMMdd')) works because I have pasted this into one of the columns of my data view and it outputs the date in the correct format (yyyyMMdd) with no page error.

So it seems that this statement just doesn't work in the filter.

Can anyone advise what I'm doing wrong / how I can do this.

Thanks in advance

TAO

Answer 1

So it seems there's an issue converting European format dates. I implemented the filter by breaking up the date parts and concatenating to yyyyMMdd:

<!-- Filter using concat and substring -->
<xsl:variable name="Rows" select="/dsQueryResponse/Work/Rows/Row[number(concat(substring(string(@StartDate),7,4),substring(string(@StartDate),4,2),substring-before(string(@StartDate),'/'))) &gt;= $ConvertedStartDateFilter and number(concat(substring(string(@StartDate),7,4),substring(string(@StartDate),4,2),substring-before(string(@StartDate),'/'))) &lt;= $ConvertedEndDateFilter]"/>        

Thanks go to Andrej Dobrovoljc for this post.