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

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

LVL 2
Richard KreidlSoftware DeveloperAsked:
Who is Participating?
 
Geert BormansInformation ArchitectCommented:
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
 
numberkruncherCommented:
You can use the following XSLT statement:
<xsl:value-of select="format-dateTime(Time_To_Check,'[h]:[m01] [P]')"/>

Open in new window

0
 
Richard KreidlSoftware DeveloperAuthor Commented:
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Richard KreidlSoftware DeveloperAuthor Commented:
numberkruncher

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

it says not a valid XSLT function.
0
 
numberkruncherCommented:
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
 
Geert BormansInformation ArchitectCommented:
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
 
numberkruncherCommented:
Sorry EE formatted my result:

0
 
Geert BormansInformation ArchitectCommented:
$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
 
numberkruncherCommented:

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

Open in new window

0
 
Geert BormansInformation ArchitectCommented:
> 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
 
Geert BormansInformation ArchitectCommented:
yes, this EE forum frustrates the XML, unless pasted in the code snippet pane :(
0
 
numberkruncherCommented:
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
 
abelCommented:
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
 
abelCommented:
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
 
Richard KreidlSoftware DeveloperAuthor Commented:
thanks
0
 
Richard KreidlSoftware DeveloperAuthor Commented:
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
 
numberkruncherCommented:
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
 
Richard KreidlSoftware DeveloperAuthor Commented:
That made it worse.
0
 
numberkruncherCommented:
What output did that give you?
0
 
Richard KreidlSoftware DeveloperAuthor Commented:
13:30 1
15:15 3
15:15 3
0
 
numberkruncherCommented:
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
 
Richard KreidlSoftware DeveloperAuthor Commented:
it's working now!!

thanks
0
 
numberkruncherCommented:
No problem :-)
0
 
Geert BormansInformation ArchitectCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.