Comparing Dates in SharePoint Using XSL
Especially when you are using Data View Web Parts (DVWPs), but anytime you are using a Web Part that allows custom XLS to control display, you may want to do a test on dates. Doing this is relatively easy if you know a few of the ddwrt namespace date functions.
In this example, let’s say that you’d like to display calendar events that occur today or later. (Old calendar events aren’t of much use to display.)
The first step is to get today’s date. You can do this with the following XSL:
<xsl:value-of select="ddwrt:Today()">
(’02/19/2008′ if your language is set to 1033)
You can also get the full ISO date and time with:
<xsl:value-of select="ddwrt:TodayISO()">
(‘2008-02-19T11:15:02Z’)
For comparison purposes, though, you’d like to have the date in YYYMMDD format. You can do this conversion with the FormatDateTime ddwrt function:
<xsl:value-of select="ddwrt:FormatDateTime(string(ddwrt:Today()), 1033, 'yyyyMMdd')">
(‘20080219’)
So, to compare your event date to today’s date, it’s a quick test:
<xsl:if test="ddwrt:FormatDateTime(string(ddwrt:Today()), 1033, 'yyyyMMdd') <= ddwrt:FormatDateTime(string(@EventDate), 1033, 'yyyyMMdd')">
Very nice. I’ve been looking to do this now for a few weeks. Then I noticed a post over on the STP and a link to this nice article. This should allow me now to wrap up a DVWP that is very functional. Thanks for the great explanation. BTW, where can I find a good XPath reference? The language can be a bit of a burden sometimes, so maybe a quick reference would be nice, if you know of one…
A nice XPath reference? Erm, wouldn’t that be nice! My learning style is more trial by fire, so I can’t think of anything to recommend to you. Are there specific areas you want to understand better?
M.
It’s just syntax really that I struggle with. When using SP Designer some of the help text is not so intuitive, so it’s hard to follow along sometimes. You are right with trial by fire and I’m still in trial mode after applying your code. I went through and manually inserted the formula and then tried to just paste your formula in to see if I made a typo. Either way, it didn’t work for me. Can you give me a little bit more guidance?
I think I found my answer here. Ironically you posted the solution there too! :)
http://social.msdn.microsoft.com/Forums/en-US/sharepointcustomization/thread/7ab52558-5258-48f1-9ab4-914a1190b109
You state there isn’t any date arithmetic within XSL/XPath or whatever it’s called. Since that is true, I cannot get a clean formula of Today-@DueDate so to speak. I was really hoping I could use a DVWP to get around the [Today] not being able to calculate in a calculated column. Do you know of anything that will allow a today calculation with an established date?
I was just answering another question about this same topic yesterday over at the EndUserSharePoint.com Stump the Panel forums. Take a look; I think that’ll give you the solution.
M.
Thanks for the link. Just from a brief look, I don’t think I can do straight math with it. It looks as if, I’m able to only do greater than or less than statements. I’m trying to get the number of days that has past as a result. From what I’ve seen, it looks like that’s not possible. Please tell me there is a way to do that… ex. ddwrt:Today() minus {@Created} = 24 and the number show up. I’ve been able to get only True or False to show up as of yet. Everything else I’ve tried has crashed the DVWP. :(
You absolutely can get the number of days between two dates. You’ll want to do something like this:
There may be a little more format fiddling, but that’s the basic idea.
M.
This is exactly what I needed. I’m not sure how to use the variable as of yet, maybe we can take that offline? Here’s my final code:
I divided by 60 b/c it looked as if it needed it. The item I created today is 0, so it should be easy to tell by tomorrow if my formula is correct. One little quirk is that I have about 10 decimal places after the whole number. Any tweaks I can add to just get the whole number? Thanks again for all of your help, it’s definitely making a difference here and I hope it helps someone else also.
Ok, maybe this is something to do with the formula but I sure am glad it’s this time of year b/c I wouldn’t have been able to catch this so easily. Here is the code that I’m using:
format-number((ddwrt:FormatDateTime(string(ddwrt:Today()), 1033, ‘yyyyMMdd’) – ddwrt:FormatDateTime(string(@DueDate), 1033, ‘yyyyMMdd’)), ‘0’)
This works great as long as it’s doing the calculations for that year. When say the @DueDate is 12/31/2009 or further back, I get a number in the 8-9000 range. That’s what was throwing me off earlier and making me do the division that isn’t needed. Have you experienced this behavior before with this type of formula? Do you think using the ddwrt:FormatDate instead would help out? I didn’t change it as you suggested because it wasn’t plug n play. When I deleted Time from FormatDateTime, it failed to render. Thanks again for all of your guidance!
Oh boy, Matt. All I can say is mea culpa, mea culpa. I’ve done this before and run into the same difficulties with date arithmetic and I should hve remembered it and engaged my brain before I typed. Teaches me to do 10 things at once.
Date arithmetic is indeed tricky because the units of measure (days, weeks, and years) don’t use the same units. Luckily, there are some great XSL templates out there to help with this. You can read about them and how to use them in my blog post entitled Date Arithmetic in SharePoint DVWPs.
Let me know how I can help to get this implemented, especially since I sent you on a wild goose chase.
M.
I think I’m getting closer! I’ve ridden myself of NaN’s =)… So the code is as follows:
So, my @Due_x0020_Date is 01-05-2010 and Today is 01-15-2010. I’m getting a full number of: 10111324. I’m thinking that this is the total seconds from that day. I took out HHmmss from the code to try that and I came up with: 20100095011428 I’m pretty sure that’s b/c Today has HHmmss in it’s number and @Due_x0020_Date doesn’t. That being said, I feel I need to do some division to get the day. Is that right?
Hmm. Which method are you using now?
M.
OOPS! I’m using this:
You’re still going to have the issues for which I apologized above. You can’t simply subtract one date from another and get consistent results if the dates are across month or year boundaries. e.g., 20090115 – 20081231 = 8884, which is meaningless. Using the ‘yyyyMMdd’ format is excellent for sorting and comparisons (is DateA GT DateB), but not for date arithmetic.
You’ll want to look at:
M.
I pulled my new code from the second link. I’ll abandon it in favor of your other blog post: Date Arithmetic in Sharepoint DVWP’s. I’ve tried to download the date_templates.xsl and it seems as if the link is broken. Do you have this file or an updated link?
Thanks,
Matt
At the bottom of the post on this page:
Filtering and Formatting with Date Values
you’ll see:
Attachment(s): date_templates.xsl
I’ll check the link in my original blog post.
M.
Hi Marc,
Could you tell me where I need to look to make sure the ddwrt namespace is referenced in my SharePoint install?
I am customizing the Content Query Web Part and would like to add the date and time but receive an “Unable to display Web Part…” error when I add the FormatDateTime function.
Emma:
ddwrt is just there. I’m not sure exactly where it is in the SharePoint DLLS, but trust me that it is. You don’t have to do anything to enable it. The error that you are getting is undoubtedly due to a mistake you are making. You *may* need to reference the ddwrt namespace in your stylesheet, but odds are it already is referenced, as CQWPs use it.
M.
Hi Marc. Thanks for getting back to me so quickly.
I had thought it would be referenced already for the same reason but once I found the URL I saw that it was not actually included on the stylesheet. That is now working perfectly so I am tackling the next obstacle in figuring out how to maneuver through SharePoint.
Thanks for your help.