locked
ms:format-date shows wrong date RRS feed

  • Question

  • I'm using ms:format-date as follows:

    <xsl:value-of select="ms:format-date(ItemDate, 'MM/dd/yyyy')"/>

    The date coming from the database looks like this:

    2011-02-15 20:00:00.000

    The result of the transform is :

    02/16/2011

    My guess is that it has something to do with timezones.  I'm in GMT -5 and the time portion of that datetime would be 8:00 PM, however, I don't understand why it wouldn't just display the date portion as requeted.   To work around the issue, I have my SQL query returning a varchar(10) of the date, which it returns "02/15/2011" as expected.  Unfortunately, I'm using this transform in quite a few system generated emails and will have to modify quite a few stored procedures to return a formatted date :(

    Unless someone here has an idea on how I can fix it (without resorting to writing my own extension).  It just pains me that such a basic concept as formatting a date is problematic.  Ugh.

    Thanks for any insight and advice.

    Jerry

    Thursday, February 17, 2011 10:13 PM

Answers

  • Your dateTimes have a time zone (-05:00) thus as Pawel explained the dateTime is translated to UTC format by the format-date function and that way you don't get the result you want.  So either make sure you do not export the dateTimes with time zone information or make sure format-date uses only the raw date info with e.g. ms:format-date(substring(TimeBegin, 1, 10))
    MVP Data Platform Development My blog
    Tuesday, February 22, 2011 5:25 PM

All replies

  • Which XSLT processor do you use? I think several MSXML versions as well as .NET XslCompiledTransform and perhaps XslTransform support that function so before trying to reproduce the problem and looking for workarounds I need to know which XSLT processor you use when you encounter the problem.
    When I use .NET 3.5 SP 1 and the input XML sample

    <?xml version="1.0" encoding="utf-8" ?>
    <root>
     <date>2011-02-18</date>
     <date>2011-02-18T13:19:00</date>
     <date>2011-02-18T00:10:00</date>
     <date>2011-02-15T20:00:00.000</date>
     <date>2011-02-15 20:00:00.000</date>
    </root>
    
    

    where the last "date" element has the input data you posted (which is not in the correct format yyyy-mm-ddThh:mm:ss) then the following stylesheet

    <?xml version="1.0" encoding="utf-8"?>
    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
      xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl"
    >
      <xsl:output method="html" indent="yes"/>
    
     <xsl:template match="/">
      <html>
       <body>
        <xsl:apply-templates/>
       </body>
      </html>
     </xsl:template>
    
     <xsl:template match="root">
      <ul>
       <xsl:apply-templates/>
      </ul>
     </xsl:template>
    
     <xsl:template match="date">
      <li>
       <xsl:value-of select="msxsl:format-date(., 'MM/dd/yyyy')"/>
      </li>
     </xsl:template>
    </xsl:stylesheet>
    
    

    outputs

    <html>
     <body>
      <ul>
     <li>02/18/2011</li>
     <li>02/18/2011</li>
     <li>02/18/2011</li>
     <li>02/15/2011</li>
     <li>
       </li>
    </ul>
     </body>
    </html>
    

    According to the documentation of the format-date function "If the argument is not a date, the output is an empty string" so I the format you posted does not seem to be recognized. 

    Let us know which XSLT processor you use, then we can investigate further.

     

     

     

     

     


    MVP Data Platform Development My blog
    Friday, February 18, 2011 12:32 PM
  • Well, that date comes right out of SQL-Server in that format from a DateTime field -- with no alterations by me.  Or, I guess I should say, that's the way I see it when I run my query in Query Analyzer, so maybe it's doing something to make it more human-readable.

    I am using XslCompiledTransform.  The project's target framework is 3.5. The namespace  to pick up format-date is included this way:

    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl"  xmlns:ms="urn:schemas-microsoft-com:xslt" xmlns:dt="urn:schemas-microsoft-com:datatypes">

     

     

    Monday, February 21, 2011 9:32 PM
  • I can repro this behavior with the following stylesheet:

    <?xml version="1.0" encoding="utf-8"?>
    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
      xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl">
      <xsl:output method="xml" indent="yes"/>
    
      <xsl:template match="/">
       <xsl:value-of select="msxsl:format-date('2011-02-15T20:00:00.000-05:00', 'MM/dd/yyyy')"/>
      </xsl:template>
    </xsl:stylesheet>
    

    If the date contains time zone information the date is being converted to UTC before passing it to the system GetDateFormat function (this is by design). If there is no time zone information in the string the string should not be touched. Now it is hard to tell what's going on in this case as I can not make your string work meaning your string is not in the correct format - meaning what you posted is not what is really being passed to the format-date method. Please provide the string you really pass to the format-date (xsl:value-of select should do the trick) and I can look into this.

    Pawel 

    Tuesday, February 22, 2011 3:39 AM
    Moderator
  • I'm calling a stored procedure in SQL-Server and am not doing anything to fudge the data.  I just take my DataSet and pass it along.  Dates/Times before 7:00 PM do not exhibit this behaviour, as well as with a time of 0:00:00 don't do it either.  It's just times after 7:00 PM.

    I've captured what the XML of the data looks like, and you should be able to see the time from it.  This request will work as it's before 7:00PM.  Changing the dates and times to after that will result in the wrong date. 

    In my code below, TemplatePath_Body is just the stylesheet, and I'll include it below as well.  I'm not sure how well the code will be represented here, but I put the formatted line in bold in the style sheet (near the bottom)

    Here's a snippit of my code.  EmailData is my DataSet object.  Contains two tables, "RequestInfo" and "TimeOff" :

       TextReader xml = new StringReader(EmailData.GetXml());
       XmlReader xmlDoc = XmlReader.Create(xml);
    
       XslCompiledTransform xslTrans = new XslCompiledTransform();
       StringWriter swResult = new StringWriter();
       XmlTextWriter writer = new XmlTextWriter(swResult);
    
       xslTrans.Load(TemplatePath_Body);
       xslTrans.Transform(xmlDoc, null, writer);
       writer.Close();



    Here is the string representation of the "xml" variable as a result of the EmailData.GetXml() method:
     <TimeOffDetails>
      <RequestInfo>
        <VacReqID>3604</VacReqID>
        <EmpNo>1706</EmpNo>
        <EmpName>Jerry Kurtz</EmpName>
        <Email>jkurtz@finelinegraphics.com                                                                         </Email>
        <LeaveType>9</LeaveType>
        <LeaveTypeDesc>Time Off</LeaveTypeDesc>
        <IsPaid>1</IsPaid>
        <IsPaidDesc>Yes</IsPaidDesc>
        <DateBegin>2011-02-28T00:00:00-05:00</DateBegin>
        <DateEnd>2011-02-28T00:00:00-05:00</DateEnd>
        <DateEnd1>2011-02-28T00:00:00-05:00</DateEnd1>
        <DateEnteredDesc>Tuesday, February 22, 2011 11:56:34 AM</DateEnteredDesc>
        <DateEntered>2011-02-22T11:56:34.683-05:00</DateEntered>
        <Comment />
        <ManagerComment />
        <Status>0</Status>
        <StatusDesc>New Requests</StatusDesc>
        <isComplete>1</isComplete>
        <IsCompleteDesc>Yes</IsCompleteDesc>
        <ManagerName />
        <ManagerEmail />
        <ManagerEmpNo>0</ManagerEmpNo>
        <ManagerQuestion />
        <TimeOff>
          <VacReqID>3604</VacReqID>
          <TimeBegin>2011-02-28T00:00:00-05:00</TimeBegin>
          <TimeBeginDesc>02/28/2011</TimeBeginDesc>
          <TimeEnd>2011-02-28T23:59:00-05:00</TimeEnd>
          <TimeEndDesc>02/28/2011</TimeEndDesc>
          <ReplacementEmpNo>0</ReplacementEmpNo>
          <CoveredBy>Nobody</CoveredBy>
          <TimeOff>All Day</TimeOff>
          <DOWDesc>Monday</DOWDesc>
        </TimeOff>
      </RequestInfo>
      <EmailSettings>
        <Id>28</Id>
        <EmailId>HR.TimeOffRequest</EmailId>
        <SourceLocation>vacRequest.cs</SourceLocation>
        <Description>Time Off Request</Description>
        <FromName>Time Off</FromName>
        <FromEmail>system@finelinegraphics.com</FromEmail>
        <Enabled>true</Enabled>
        <Template_Subject>/Parts/Templates/Email/HR_TimeOffRequest_Subject.xslt</Template_Subject>
        <Template_Body>/Parts/Templates/Email/HR_TimeOffRequest_Body.xslt</Template_Body>
        <OptOutAllowed>false</OptOutAllowed>
        <CCSupportStaff>false</CCSupportStaff>
        <Monitored>false</Monitored>
        <Comments />
        <DefaultTheme />
      </EmailSettings>
    </TimeOffDetails>

    <?xml version="1.0" encoding="utf-8"?>
    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl" xmlns:ms="urn:schemas-microsoft-com:xslt" xmlns:dt="urn:schemas-microsoft-com:datatypes">
     <xsl:include href="_base.xslt"/>
     <xsl:template match="TimeOffDetails">
     <html>
      <head>
      <xsl:apply-templates select="EmailSettings"/>
      </head>
      <body>
      <table cellpadding="1" cellspacing="0" class="BorderThick" style="width:750px;">
       <tr>
       <td>&amp;nbsp;</td>
       <td>
        <xsl:apply-templates select="RequestInfo"/>
        <br/>
        This is a system generated email, please do not respond to this email.<br/>
       </td>
       <td>&amp;nbsp;</td>
       </tr>
      </table>
      </body>
     </html>
     </xsl:template>
    
     <xsl:template match="RequestInfo">
     <div class="EmailHeader">
      <xsl:if test="Status = '0'">
      <xsl:value-of select="EmpName"/> has requested the following time off:
      </xsl:if>
      <xsl:if test="Status = '1'">
      <xsl:value-of select="ManagerName"/> has a question about your request for time off:
      </xsl:if>
      <xsl:if test="Status = '2'">
      Management has approved your request for time off:
      </xsl:if>
      <xsl:if test="Status = '3'">
      Management has denied your request for time off:
      </xsl:if>
     </div>
     <br/>
     <!-- Manager Question -->
     <xsl:if test="ManagerQuestion != ''">
      <div class="CommentHeader">Question from <xsl:value-of select="ManagerName"/>:</div>
      <table cellpadding="0" cellspacing="0" style="width:100%" class="CommentDetail">
      <tr style="height:5px"><td colspan="3"></td></tr>
      <tr>
       <td>&amp;nbsp;</td>
       <td width="100%"><xsl:value-of select="ManagerQuestion"/></td>
       <td>&amp;nbsp;</td>
      </tr>
      <tr style="height:5px"><td colspan="3"></td></tr>
      </table>
      <xsl:if test="ManagerEmail != ''">
      You should reply directly back to <xsl:value-of select="ManagerName"/>, whose email address is <xsl:value-of select="ManagerEmail"/>.<br/><br/>
      </xsl:if>
     </xsl:if>
     <!-- Manager comment -->
     <xsl:if test="ManagerComment != ''">
      <div class="CommentHeader">Management's Comments :</div>
      <table cellpadding="0" cellspacing="0" style="width:100%" class="CommentDetail">
      <tr style="height:5px"><td colspan="3"></td></tr>
      <tr>
       <td>&amp;nbsp;</td>
       <td width="100%"><xsl:value-of select="ManagerComment"/></td>
       <td>&amp;nbsp;</td>
      </tr>
      <tr style="height:5px"><td colspan="3"></td></tr>
      </table>
      <br/>
     </xsl:if>
     <table class="BorderThin" cellpadding="4" cellspacing="0" style="width:100%">
      <tr>
       <td class="DarkUL">&amp;nbsp;</td>
       <td class="DarkUL">Employee</td>
       <td class="DarkUL">&amp;nbsp;&amp;nbsp;</td>
       <td class="LiteUL">&amp;nbsp;</td>
       <td class="LiteUL" style="width:100%;"><xsl:value-of select="EmpName"/></td>
      </tr>
      <tr>
       <td class="DarkUL">&amp;nbsp;</td>
       <td class="DarkUL">Leave Type</td>
       <td class="DarkUL">&amp;nbsp;&amp;nbsp;</td>
       <td class="LiteUL">&amp;nbsp;</td>
       <td class="LiteUL" style="width:100%;"><xsl:value-of select="LeaveTypeDesc"/></td>
      </tr>
      <tr>
       <td class="DarkUL">&amp;nbsp;</td>
       <td class="DarkUL">Paid</td>
       <td class="DarkUL">&amp;nbsp;&amp;nbsp;</td>
       <td class="LiteUL">&amp;nbsp;</td>
       <td class="LiteUL"><xsl:value-of select="IsPaidDesc"/></td>
      </tr>
      <tr>
       <td class="DarkUL">&amp;nbsp;</td>
       <td class="DarkUL">Time Entered</td>
       <td class="DarkUL">&amp;nbsp;&amp;nbsp;</td>
       <td class="LiteUL">&amp;nbsp;</td>
       <td class="LiteUL"><xsl:value-of select="DateEnteredDesc"/></td>
      </tr>
      <tr>
       <td class="DarkUL">&amp;nbsp;</td>
       <td class="DarkUL">Employee Number</td>
       <td class="DarkUL">&amp;nbsp;&amp;nbsp;</td>
       <td class="LiteUL">&amp;nbsp;</td>
       <td class="LiteUL"><xsl:value-of select="EmpNo"/></td>
      </tr>
      <tr>
       <td class="Dark">&amp;nbsp;</td>
       <td class="Dark">Request Id</td>
       <td class="Dark">&amp;nbsp;&amp;nbsp;</td>
       <td class="Lite">&amp;nbsp;</td>
       <td class="Lite"><xsl:value-of select="VacReqID"/></td>
      </tr>
     </table>
     <br/>
     <table class="BorderThin" border="0" cellpadding="4" cellspacing="0" style="width:100%">
      <tr align="left">
       <th style="text-align:left; whitespace:nowrap;" class="Header">Weekday&amp;nbsp;&amp;nbsp;</th>
       <th style="text-align:left; whitespace:nowrap;" class="Header">Date&amp;nbsp;&amp;nbsp;</th>
       <th style="text-align:left; whitespace:nowrap;" class="Header">Time Off&amp;nbsp;&amp;nbsp;</th>
       <th style="text-align:left; whitespace:nowrap;" class="Header">Covered By&amp;nbsp;&amp;nbsp;</th>
      </tr>
      <xsl:apply-templates select="TimeOff"/>
      </table>
     <br/>
     <xsl:if test="Comment != ''">
      <div class="CommentHeader">Employee's Comments :</div>
      <table cellpadding="0" cellspacing="0" style="width:100%" class="CommentDetail">
      <tr style="height:5px"><td colspan="3"></td></tr>
      <tr>
       <td>&amp;nbsp;</td>
       <td width="100%"><xsl:value-of select="Comment"/></td>
       <td>&amp;nbsp;</td>
      </tr>
      <tr style="height:5px"><td colspan="3"></td></tr>
      </table>
     </xsl:if>
     </xsl:template>
    
    
     <xsl:template match="TimeOff">
     <tr class="R{position() mod 2}">
      <td><xsl:value-of select="DOWDesc"/>&amp;nbsp;&amp;nbsp;</td>
      <td><xsl:value-of select="ms:format-date(TimeBegin, 'MM/dd/yyyy')"/>&amp;nbsp;&amp;nbsp;</td>
      <td><xsl:value-of select="TimeOff"/>&amp;nbsp;&amp;nbsp;</td>
      <td><xsl:value-of select="CoveredBy"/>&amp;nbsp;&amp;nbsp;</td>
     </tr>
     </xsl:template>
    
    
    </xsl:stylesheet>
    
    
    Tuesday, February 22, 2011 5:07 PM
  • Wow, that formatting looks like crap -- I need to learn how you're posting the XML in color and such...
    Tuesday, February 22, 2011 5:08 PM
  • Here's the XML that restuts in an incorrect date from the ms:format-date() routine (look at TimeBegin field).  This time off request was from 7:30PM to 9:00 PM on 2/24.  After the transform, the date reads 2/25:

     

    <TimeOffDetails>
     <RequestInfo>
      <VacReqID>3605</VacReqID>
      <EmpNo>1706</EmpNo>
      <EmpName>Jerry Kurtz</EmpName>
      <Email>jkurtz@finelinegraphics.com                                     </Email>
      <LeaveType>9</LeaveType>
      <LeaveTypeDesc>Time Off</LeaveTypeDesc>
      <IsPaid>1</IsPaid>
      <IsPaidDesc>Yes</IsPaidDesc>
      <DateBegin>2011-02-24T00:00:00-05:00</DateBegin>
      <DateEnd>2011-02-24T00:00:00-05:00</DateEnd>
      <DateEnd1>2011-02-24T00:00:00-05:00</DateEnd1>
      <DateEnteredDesc>Tuesday, February 22, 2011 12:14:19 PM</DateEnteredDesc>
      <DateEntered>2011-02-22T12:14:19.02-05:00</DateEntered>
      <Comment />
      <ManagerComment />
      <Status>0</Status>
      <StatusDesc>New Requests</StatusDesc>
      <isComplete>1</isComplete>
      <IsCompleteDesc>Yes</IsCompleteDesc>
      <ManagerName />
      <ManagerEmail />
      <ManagerEmpNo>0</ManagerEmpNo>
      <ManagerQuestion />
      <TimeOff>
       <VacReqID>3605</VacReqID>
       <TimeBegin>2011-02-24T19:30:00-05:00</TimeBegin>
       <TimeBeginDesc>02/24/2011</TimeBeginDesc>
       <TimeEnd>2011-02-24T21:00:00-05:00</TimeEnd>
       <TimeEndDesc>02/24/2011</TimeEndDesc>
       <ReplacementEmpNo>0</ReplacementEmpNo>
       <CoveredBy>Nobody</CoveredBy>
       <TimeOff>7:30 PM to 9:00 PM</TimeOff>
       <DOWDesc>Thursday</DOWDesc>
      </TimeOff>
     </RequestInfo>
     <EmailSettings>
      <Id>28</Id>
      <EmailId>HR.TimeOffRequest</EmailId>
      <SourceLocation>vacRequest.cs</SourceLocation>
      <Description>Time Off Request</Description>
      <FromName>Time Off</FromName>
      <FromEmail>system@finelinegraphics.com</FromEmail>
      <Enabled>true</Enabled>
      <Template_Subject>/Parts/Templates/Email/HR_TimeOffRequest_Subject.xslt</Template_Subject>
      <Template_Body>/Parts/Templates/Email/HR_TimeOffRequest_Body.xslt</Template_Body>
      <OptOutAllowed>false</OptOutAllowed>
      <CCSupportStaff>false</CCSupportStaff>
      <Monitored>false</Monitored>
      <Comments />
      <DefaultTheme />
     </EmailSettings>
    </TimeOffDetails>
    Tuesday, February 22, 2011 5:17 PM
  • Your dateTimes have a time zone (-05:00) thus as Pawel explained the dateTime is translated to UTC format by the format-date function and that way you don't get the result you want.  So either make sure you do not export the dateTimes with time zone information or make sure format-date uses only the raw date info with e.g. ms:format-date(substring(TimeBegin, 1, 10))
    MVP Data Platform Development My blog
    Tuesday, February 22, 2011 5:25 PM
  • Your dateTimes have a time zone (-05:00) thus as Pawel explained the dateTime is translated to UTC format by the format-date function and that way you don't get the result you want.  So either make sure you do not export the dateTimes with time zone information or make sure format-date uses only the raw date info with e.g. ms:format-date(substring(TimeBegin, 1, 10))
    MVP Data Platform Development My blog


    I had a hunch it was related to time zones, but it seems quite silly that it can't calulate the date properly after it converts the time.

    What do you mean by "So either make sure you do not export the dateTimes with time zone information"?  How do I do that?

    If I have to resort to using substring, I guess I will...

    Tuesday, February 22, 2011 5:31 PM
  • I don't see anything silly in the date calculation, when you have e.g. 2011-02-21T20:00:00-05:00 then that is equivalent to 2011-02-22T01:00:00 UTC. That format-date function should have an option or parameter to indicate which time zone the output should be in or one would need XSLT 2.0 support with dateTime arithmetic and time zone adjustment http://www.w3.org/TR/xpath-functions/#func-adjust-dateTime-to-timezone. Microsoft does not have XSLT 2.0 support so unless you want to switch to a third party XSLT 2.0 processor like Saxon 9 or XQSharp that is not an option.

    As for exporting dateTimes without time zone information, sorry I don't know whether it is possible, I am just trying to suggest workarounds, I don't know how you generate the data exactly and I don't know whether you can export it without time zone information, you say you used a stored procedure on SQL server, ask in an SQL server group whether and how your server-side code can be changed to avoid the problem.

    Fixing it in the stylesheet with substring should do.


    MVP Data Platform Development My blog
    Tuesday, February 22, 2011 6:02 PM
  • If I pass in a DateTime field whose date is 2/25/2011, and I'm simply asking it to format the date, I sincerely expect "2/25/2011" to be returned back.  Nothing more, nothing less.  The routine aptly named format-date() needs to be smart enough to realize that I'm asking it to simply format a date, and if it converts the time, then it needs to make the necessary adjustments to represent the value I passed in, not some internally converted value.  So, yes, I still think it's silly.

    This is why I say it's silly:  The database is Microsoft SQL-Server 2008, the programming is done in C# ASP.Net, using the System.Data namespace (and SqlClient namespace).  I'm simply passing the resulting DataSet object from the query on to another set of Microsoft classes for the XML transformation.  Somewhere along the line, the rules for working with dates gets muddied and there is some kind of disconnect in the bowels of their namespaces. 

    I made no efforts to thwart or circumvent any of the namespace routines or data types, in fact, I embraced them -- so yes, I expect the proper result.  Too much to ask?

    Anyway, I already know that I can use the CONVERT function in SQL to change the formatting of the date, and in this example, I did exactly that as a work around until I know what's going on and to implement the correct fix.  I was just wondering if there is a global option in SQL Server, but I would be quite hesitant to use it anyways.   What I don't want to do is go back and modify hundreds of stored procedures to wrap my DateTime fields in convert statements.

    Thanks for the help though - it was actually appreciated.

     

     

    Tuesday, February 22, 2011 6:31 PM
  • Hi,

    How's the problem now?  

    Do you need any further assistance?

    Good day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, February 24, 2011 12:40 PM
    Moderator
  • Hi,

    How's the problem now?  

    Do you need any further assistance?

    Good day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    No, I don't need further assistance with this issue - thanks for asking.  I used the substring() method in the style sheet to pick just the date portion of the value, however, I still assert that the date-format() function is problematic in that it can't properly keep track of the incoming date when generating the format.  If I pass in the date of 2/15/2011, I expect 2/15/2011 to be returned, not a UTC converted date (or whatever) of 2/16/2011.  Getting the incorrect date returned is simply wrong - no resoning can justify that.

     

    Thursday, February 24, 2011 2:48 PM