Skyhawk Systems - Simplify data transformations
Products | Download | Buy | About Us
 
Table of Contents Connect XML-2-DB User's Guide mapRule.htmTable of ContentsmapConstantRule.htm

5(f). Date and Time formats

When a value has to be inserted into a database column that has a date/time data type, (date for Oracle, datetime and smalldatetime for SQL Server) the format of the date value has to be specified in order to correctly interpret the date value. For example, 2/10/2000 and 10/2/2000 could both be interpreted as Feb 10, 2002 or as October 2, 2002.

The <map> element has an optional attribute, source-datetime-format, which can be used to specify the format that the date value will have in the XML data file.

  • For example, suppose that we have an xml data file that looks like the following:
          <SalesInfo>
            <software name="ABC Software" release-date="11/30/2000" retail-price="300.00">
               <software-sale purchase-price="120.00" purchase-date="12-31-2002"/>
            </software>
          </SalesInfo>
    	  
  • Also, assume that the Oracle table BILLING.SOFTWARE_SALES has a reference, SoftwareSales, defined for it in the mapping file.
          <!-- Oracle example -->
          <table-reference-information
             table-reference="SoftwareSales"
             table-name="SOFTWARE_SALES"
             object-owner="BILLING">
          </table-reference-information>
    	  
  • Assume that the value of the attribute purchase-date ("12-31-2002"), of the XML element software-sale, has to be stored in the database column BILLING.SOFTWARE_SALES.PURCHASE_DATE.
  • Assume that the value of the attribute release-date ("11/30/2000"), of the XML element software, has to be stored in the database column BILLING.SOFTWARE_SALES.RELEASE_DATE.

Then mapping rules to to do this would be as follows:

      <!--  Oracle example -->
      <map
         element-name="software-sale" 
         attribute-name="purchase-date"
         table-reference="SoftwareSales"
         column-name="PURCHASE_DATE"
         source-datetime-format="MM-DD-YYYY">
      </map>
	  
      <map
         element-name="software" 
         attribute-name="release-date"
         table-reference="BillingSoftware"
         column-name="RELEASE_DATE"
         source-datetime-format="MM/dd/yyyy">
      </map>
	  

Other examples of date formats are provided in the table below:

source-datetime-format Data in XML File
MM/dd/yyyy 6/20/1990
MM-dd-yyyy 6-20-1990
MMM dd, yyyy December 22, 2002
MMM dd yy Jan 15 03
MM/dd/yy hh:mm:ss a 03/26/90 12:20:55 PM
dd MMM yyyy H:m:s z 28 Jun 1991 1:30:49 PST

Please refer to the Java documentation for the class java.text.SimpleDateFormat for more details on valid date and time formats.

Copyright Skyhawk Systems. All Rights Reserved.
Send comments and questions to support@skyhawksystems.com.
mapRule.htmTable of ContentsmapConstantRule.htm