?
Solved

Change element of default date/time to AM/PM format?

Posted on 2009-04-19
24
Medium Priority
?
627 Views
Last Modified: 2013-11-18
I need to change an element that has the default date/time from an Access database table to an AM/PM time format.

Sample of XML file:
<?xml version="1.0" standalone="yes"?>
<NewDataSet>
  <Table>
    <Item_ID>163</Item_ID>
    <Time_To_Check>1899-12-30T00:30:00-06:00</Time_To_Check>
    <Item_To_Check>ricky </Item_To_Check>
    <Checked_By>KRE1973 9:43 AM</Checked_By>
  </Table>
  <Table>
    <Item_ID>84</Item_ID>
    <Time_To_Check>1899-12-30T08:00:00-06:00</Time_To_Check>
    <Item_To_Check>DBATCH</Item_To_Check>
    <Checked_By>KRE1973 9:57 AM</Checked_By>
  </Table>
       <Table>
    <Item_ID>103</Item_ID>
    <Time_To_Check>1899-12-30T11:30:00-06:00</Time_To_Check>
    <Item_To_Check>On Fridays UNIX tapes dwntwn</Item_To_Check>
    <Checked_By>KRE1973 9:25 AM</Checked_By>
  </Table>
  <Table>
    <Item_ID>107</Item_ID>
    <Time_To_Check>1899-12-30T12:30:00-06:00</Time_To_Check>
    <Item_To_Check>Specprods</Item_To_Check>
    <Checked_By>KRE1973 10:22 AM</Checked_By>
  </Table>
   <Table>
    <Item_ID>112</Item_ID>
    <Time_To_Check>1899-12-30T13:45:00-06:00</Time_To_Check>
    <Item_To_Check>Thursday Autosys recycle</Item_To_Check>
    <Checked_By>KRE1973 9:27 AM</Checked_By>
    <Comments>Had problems with check</Comments>
  </Table>
 <Table>
    <Item_ID>127</Item_ID>
    <Time_To_Check>1899-12-30T15:35:00-06:00</Time_To_Check>
    <Item_To_Check>JCLRABND</Item_To_Check>
    <Checked_By>KRE1973 10:22 AM</Checked_By>
  </Table>
</NewDataSet>

Current format:
1899-12-30T00:30:00-06:00
Desired format:
12:30 AM

Current format:
1899-12-30T15:35:00-06:00
Desired format:
3:35 PM

thanks

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> 
<xsl:output method="html" indent="yes" />
<xsl:template match="/">
 
          <HTML>
               <HEAD>
                 <style type="text/css">
BODY {margin:0; background-color="ivory"}
H1 {font:bold 18pt Verdana; margin-top:1em; color:"#8b0000"}
DIV {font:bold; font-size:11px;height:20px}
pre {word-wrap:break-word;font-family: arial,san-serif;margin-top:2px;margin-bottom:2px;font-size:11px;font-weight:bold}
	</style>
               </HEAD>
                  
               <!-- Start of HTML Body -->
               <BODY>
	   <H1 align="center">First Shift Check Sheet</H1>	                      
                    <DIV id="listing">
                         <xsl:apply-templates select="NewDataSet"/>
                    </DIV>
                </BODY>
               <!-- End of HTML Body -->
          </HTML>
     </xsl:template>
     <!-- Start of Templates -->
     <xsl:template match="NewDataSet">
          <TABLE width="100%" border="1" borderColor="black" valign="TOP" align="center">
               <TR bgColor="#cccccc" borderColorDark="black" borderColorLight="black">
                    <TD>
                         <font color="purple">
                              <DIV align="center">Time To Check</DIV>
                         </font>
                    </TD>
                   <TD>
                         <font color="purple">
                              <DIV align="center">Item To Check</DIV>
                         </font>
                    </TD>
                    <TD>
                         <font color="purple">
                              <DIV align="center">Checked By And Time</DIV>
                         </font>
                    </TD>
	<TD>
                         <font color="purple">
                              <DIV align="center">Comments</DIV>
                         </font>
                    </TD>
               </TR>
               <xsl:for-each select="Table">
	  <TR bgColor="#f0f0f0">
	<TD>
	  <DIV align="center">
                    <xsl:value-of select="Time_To_Check"/>
	  </DIV>
	 </TD>
                             <TD>
                              <DIV align="center">
                                   <font color="blue">
                                        <xsl:value-of select="Item_To_Check"/>
                                   </font>
                              </DIV>
                         </TD>
                         <TD>
                     <DIV align="center">
	           <xsl:value-of select="Checked_By"/>
	     </DIV>
                         </TD>
                         <TD>
                              <DIV align="center">
                                      <xsl:value-of select="Comments"/>
	             </DIV>
                         </TD>
	    </TR>
               </xsl:for-each>
          </TABLE>
    </xsl:template>
</xsl:stylesheet>

Open in new window

0
Comment
Question by:Richard Kreidl
  • 9
  • 7
  • 6
  • +1
24 Comments
 
LVL 13

Expert Comment

by:numberkruncher
ID: 24179290
You can use the following XSLT statement:
<xsl:value-of select="format-dateTime(Time_To_Check,'[h]:[m01] [P]')"/>

Open in new window

0
 
LVL 60

Accepted Solution

by:
Geert Bormans earned 2000 total points
ID: 24179297
If you are using XSLT1, you need to split the string

            <xsl:for-each select="Table">
                <TR bgColor="#f0f0f0">
                    <TD>
                        <DIV align="center">
                            <xsl:variable name="hour">
                                <xsl:value-of select="substring-before(substring-after(Time_To_Check, 'T'), ':')"/>
                            </xsl:variable>
                            <xsl:variable name="minute">
                                <xsl:value-of select="substring-before(substring-after(substring-after(Time_To_Check, 'T'), ':'), ':')"/>
                            </xsl:variable>
                            <xsl:value-of select="$hour"/>
                            <xsl:text>:</xsl:text>
                            <xsl:value-of select="$minute"/>
                            <xsl:text>&#160;</xsl:text>
                            <xsl:choose>
                                <xsl:when test="$hour < 12">
                                    <xsl:text>AM</xsl:text>
                                </xsl:when>
                                <xsl:otherwise>
                                    <xsl:text>PM</xsl:text>
                                </xsl:otherwise>
                            </xsl:choose>
                           
                        </DIV>
                    </TD>
0
 
LVL 2

Author Comment

by:Richard Kreidl
ID: 24179310
Gertone, it doesn't like this:
 <xsl:when test="$hour < 12">

error
The character '<' cannot be used in an attribute value. Error processing resource 'file:///C:/First_XSL.xsl'. Line 66, Pos...

                                <xsl:when test="$hour < 12">
------------------------------------------------------^
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Author Comment

by:Richard Kreidl
ID: 24179316
numberkruncher

I'm getting an error in my VB transformation code on this line:
format-dateTime()

it says not a valid XSLT function.
0
 
LVL 13

Expert Comment

by:numberkruncher
ID: 24179319
In that case you are using XSLT 1.0. You should proceed with Geretone's advice.

To solve the < 12 problem simple change to:

 
0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 24179321
If you could use XSLT2,
then you could do this
                            <xsl:value-of select="upper-case(translate(format-dateTime(xs:dateTime(Time_To_Check),'[h]:[m01]&#160;[P]'), '.', ''))"/>

make sure that you add
xmlns:xs="http://www.w3.org/2001/XMLSchema"
to the stylesheet element
I like explicit casting in XSLT2 and mostly [P] will give you "p.m." instead of "PM"
0
 
LVL 13

Expert Comment

by:numberkruncher
ID: 24179325
Sorry EE formatted my result:

0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 24179327
$hour < 12
should be, see below...

(I had to paste in the code snippet, EE frustrated my code)
$hour &lt; 12

Open in new window

0
 
LVL 13

Expert Comment

by:numberkruncher
ID: 24179328

First time lucky perhaps:
 
<xsl:when test="$hour &lt; 12">

Open in new window

0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 24179329
> I'm getting an error in my VB transformation code on this line:

your VB transform will only allow XSLT1,
unless you add the .net variant of Saxon
0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 24179331
yes, this EE forum frustrates the XML, unless pasted in the code snippet pane :(
0
 
LVL 13

Expert Comment

by:numberkruncher
ID: 24179332
Gertone suggested Saxon to me a while back and I can say that it is absolutely fantastic. Saxon makes life so much simpler in the long run.
0
 
LVL 39

Expert Comment

by:abel
ID: 24179338
Advanced date/time formatting is not trivial in XSLT 1.0, as you already found out. If you want to stick to the format you mentioned in your question, you can go by Gertone's solution. If you need more flexibility, consider using the EXSLT templates from Jeni Tenison (and you might even be able to use the extensions directly if your processor supports them).

How to use them and where to find them is explained by me in this thread: http:Q_24186545.html
A hands-on example is here (same thread): http:Q_24186545.html#23768281
Some more explanations and example (same thread): http:Q_24186545.html#23768336

These templates are designed to work with a picture string. Which makes creating date strings much easier, though it is slightly more work to set it all up.

-- Abel --
0
 
LVL 39

Expert Comment

by:abel
ID: 24179344
What is that with those links... Again..., but now properly (I hope)

How to use them and where to find them is explained by me in this thread: http:Q_24186545.html
A hands-on example is here (same thread): http:Q_24186545.html#23768281
Some more explanations and example (same thread): http:Q_24186545.html#23768336
0
 
LVL 2

Author Closing Comment

by:Richard Kreidl
ID: 31571977
thanks
0
 
LVL 2

Author Comment

by:Richard Kreidl
ID: 24179354
Gertone, I jumped the gun to early, you solution gives me times of

14:00 PM
15:15 PM

should be
 2:00 PM
 3:15 PM
0
 
LVL 13

Expert Comment

by:numberkruncher
ID: 24179385
You can fix this by changing the following:

                           

to the following:

                         <xsl:choose>
                            <xsl:when test="number($hour) &gt; 12">
                               <xsl:value-of select="number($hour)-12"/>
                            </xsl:when>
                            <xsl:otherwise>
                               <xsl:value-of select="$hour"/>
                            </xsl:otherwise>
                         </xsl:choose>

Open in new window

0
 
LVL 2

Author Comment

by:Richard Kreidl
ID: 24179391
That made it worse.
0
 
LVL 13

Expert Comment

by:numberkruncher
ID: 24179404
What output did that give you?
0
 
LVL 2

Author Comment

by:Richard Kreidl
ID: 24179412
13:30 1
15:15 3
15:15 3
0
 
LVL 13

Expert Comment

by:numberkruncher
ID: 24179421
What is the complete block of XSLT code that you are using? Should be something like the following:
<xsl:for-each select="Table">
    <TR bgColor="#f0f0f0">
        <TD>
            <DIV align="center">
                <xsl:variable name="hour">
                    <xsl:value-of select="substring-before(substring-after(Time_To_Check, 'T'), ':')"/>
                </xsl:variable>
                <xsl:variable name="minute">
                    <xsl:value-of select="substring-before(substring-after(substring-after(Time_To_Check, 'T'), ':'), ':')"/>
                </xsl:variable>
 
<!-- The replacement part -->
                 <xsl:choose>
                    <xsl:when test="number($hour) &gt; 12">
                       <xsl:value-of select="number($hour)-12"/>
                    </xsl:when>
                    <xsl:otherwise>
                       <xsl:value-of select="$hour"/>
                    </xsl:otherwise>
                 </xsl:choose>
 
 
                <xsl:text>:</xsl:text>
                <xsl:value-of select="$minute"/>
                <xsl:text>&#160;</xsl:text>
                <xsl:choose>
                    <xsl:when test="$hour &lt; 12">
                        <xsl:text>AM</xsl:text>
                    </xsl:when>
                    <xsl:otherwise>
                        <xsl:text>PM</xsl:text>
                    </xsl:otherwise>
                </xsl:choose>
                
            </DIV>
        </TD>

Open in new window

0
 
LVL 2

Author Comment

by:Richard Kreidl
ID: 24179428
it's working now!!

thanks
0
 
LVL 13

Expert Comment

by:numberkruncher
ID: 24179433
No problem :-)
0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 24179481
Well, well, I was out for a couple of minutes and guess what all happened :-)
Silly me forgot to take of the 12 hours for PM
thanks for adjusting numbercruncher
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Shoutout to Emily Plummer (http://www.experts-exchange.com/members/eplummer26.html) for giving me this article! She did most of it, I just finished it up and posted it for her :)    Introduction In a previous article (http://www.experts-exchang…
JavaScript has plenty of pieces of code people often just copy/paste from somewhere but never quite fully understand. Self-Executing functions are just one good example that I'll try to demystify here.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question