Date Arithmetic in SharePoint DVWPs
I posted last September about Date Calculations in a DVWP. In that post, I pointed out Andy Lewis’ great post about how to do Filtering and Formatting with Date Values over at the Microsoft SharePoint Designer Team Blog.
I’ve been answering quite a few questions about date arithmetic over at the MSDN forums lately, so I thought an updated post was in order to explain a little bit more about how you can use the templates that Andy provides. Here are the steps I would suggest:
- Download the date_templates.xsl file.
- Put date_templates.xsl into your Site Collection. I usually put my reusable XSL into /Style Library/XSL Style Sheets in MOSS, or I create a Document Library in the root site in WSS.
- Include the templates in your DVWP. Assuming that you’ve used the location above, add the <xsl:import> line into your DVWP immediately below the <stylesheet line, like so:
&lt;xsl:stylesheet xmlns:x="http://www.w3.org/2001/XMLSchema" xmlns:d="http://schemas.microsoft.com/sharepoint/dsp" version="1.0" exclude-result-prefixes="xsl msxsl ddwrt" xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime" xmlns:asp="http://schemas.microsoft.com/ASPNET/20" xmlns:__designer="http://schemas.microsoft.com/WebParts/v2/DataView/designer" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:SharePoint="Microsoft.SharePoint.WebControls" xmlns:ddwrt2="urn:frontpage:internal"&gt; &lt;xsl:import href="/Style Library/XSL Style Sheets/date_templates.xsl"/&gt;
- Simply call the templates, passing in the appropriate parameters. For example, to call the getDateFromDays template:
&lt;xsl:call-template name="getDateFromDays"&gt; &lt;xsl:with-param name="paramBaseDate" select="@MyDateColumn"/&gt; &lt;xsl:with-param name="paramDelta"&gt;7&lt;/xsl:with-param&gt; &lt;/xsl:call-template&gt;To understand what you need to pass into each template, you’ll need to dive into the date_templates.xsl file a little, but Andy’s documented the XSL well. Each template will require a set of parameters like getDateFromDays above.
I would like to apply this to a DVWP. I would like to restrict my calendar event list to Today until 13 months out.
how would I do that? My date field is the standard Start Time field
Lois:
You can add an xsl:if to your XSL that only shows the item if it passes the test you set up in a variable using these date/time XSL templates.
M.
Hi Marc!
Nice XSLT customization :)
I used the “getDayDelta” template and there was something missing. The parameters have to be formatted as the script split it, so i used this formatting:
xsl:with-param name=”paramDateA” select=”ddwrt:FormatDateTime(string(@EndDate) ,1033 ,’yyyy-MM-dd’)”
xsl:with-param name=”paramDateB” select=”ddwrt:FormatDateTime(string(ddwrt:Today()) ,1033 ,’yyyy-MM-dd’)”
And then it worked like a charm!
Thank you.
Hey Mark,
Great article, but I had one question. I took a copy of your code and moved it to the layouts folder 14 hive. I’m using the getDayDelta function in my xslt list view web part by and I’m referencing your file from the _layouts folder( using xsl:import).From Design view in SPD 2010, the column value that uses the getDayDelta function displays ‘NaN’ to me, but in the browser it works. Any ideas?
Brandon:
When you see different results in SPD than in the browser, it usually means that the access path to the code is different. I’m not sure in this case, though. If you aren’t getting an error in SPD on the import, then the XSL is coming in, but it’s returning a different value than you get in the browser. Is it because the values aren’t available in SPD, e.g., they are based on a Query String parameter?
M.
How can we display yesterday date in DVWP ? It tried doing this : ddwrt:FormatDateTime(ddwrt:Today() – 1,1033,’MMMM dd’)
Unfortunately, date arithmetic isn’t that simple, thus the complex XSL templates referenced here. If you just add 1 to the 31st of the month, you get the 32nd. See, that doesn’t make sense.
M.
Hi,
First of all, I am a big fan of your site.
Currently, I am looking to filter DVWP on week basis (current, prev, next).
How I can do this (my week starts on Sunday)?
Have you looked at the date templates referenced in this post?
M.
Here’s a weird thing … the method you describe here works perfectly for me. But only for me. When I look at the SharePoint page with this solution using another login than mine, I get the generic DVWP arror message [Unable to display this Web Part. To troubleshoot etc]. Not a permissions problem, because colleagues who have the same admin permissions I have get the same error message.
It’s not the Date_Templates.xsl file, because with the generic login, I can view the XSL code in the browser. Everything is Published. What could I be missing?
Alan:
My first thought is permissions on one of the components, but it sounds like you’ve checked that. You’re sure that the XSL file is readable by others? Check in Fiddler or Firebug on their machines to make sure.
M.
Hi, Marc,
Thanks for the speedy response … It’s a good thought, but I tried the full pathway to the XSL Template file on another login (ie, http://mysite/Style%20Library/XSL%20Style%20Sheets/date_templates.xsl) and was able to see the code in the file perfectly.
Actually … the clue was in the code as viewed in the browser. I’d added a section to the date_templates.xsl file to manage traffic light colours on the DVWP table. That section is not there when the code is viewed in the generically logged-in browser, but IS visible when viewed in the browser logged in as me.
So, even though SPD is telling me the file was checked in as a major version, it wasn’t. Checked out the file, checked it back in as a major, now it all works fine.
I hate SPDesigner …
But thanks for your help – I’d probably have gone round in circles for hours but for your question about making sure the XSL file is readable by others …
Odd. Well, glad you solved it!
M.
I appled this and it worked like a charm. just as a “nice to have”: any way to take into account a work week when finding the difference between two date fields? (ex: our company works a 4 day week)
I haven’t seen an XSL template to do this, but that doesn’t mean that there isn’t one out there. You could also adapt one of the existing templates to meet your needs.
M.