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.