Skip to Content
Every Oracle developer knows that the best way to avoid version and format compatibility issues with DATE and TIME is to use the TO_DATE() function:         UPDATE TABLE_EXAMPLE           SET DATE_EXAMPLE = TO_DATE(‘2005-07-11’,’YYYY-MM-DD’)

Using TO_DATE() you explicitly declare the format of the date field, independently from the settings or the locale of the database

With XI you can easily access databases using the XML SQL message protocol:

Each standard SQL statement is rendered with a predefined datatype structure like the one in the figure below:

image

 

With the XML SQL you:

  • Define the type of operation (SELECT, UPDATE, INSERT, DELETE) in the (action) attribute
  • Set the table name in the (table) field
  • Define the fields (access) and the key (key) of your SQL Statement, and the datatype of each field

You can find more info on XML SQL in the XI documentation: “Document formats for the receiver JDBC adapter”

Unfortunately, this doesn’t work for DATE/TIME types if you try to access an Oracle database: the DBMS is not able to recognize the date/time format and you get a type mismatch error.

How can we avoid that using the TO_DATE() function with the XML SQL format? With the attribute hasQuot we can force the JDBC processor not to add quotes (‘’) around the content of a string field: Than we can put into that field the whole TO_DATE() statement using standard mapping functions. Let see that in detail. First, we should declare as string each DATE/TIME field and add the hasQuot attribute, like in the following figure: image Then in our mapping we set the hasQuot attribute to “No”: image And we build the TO_DATE()statement with the appropriate concat() functions and constant image As you can see, the content of the field is exactly the same you would use in your SQL Statement and the hasQuot=’No’ attribute let this statement go unchanged to the DBMS. Obviously in your mapping you can use a variable field for your date instead of a constant but be sure to match the format specified in the TO_DATE() function.

To report this post you need to login first.

8 Comments

You must be Logged on to comment or reply to a post.

  1. Craig Cmehil
    Hey can you check your formatting it doesn’t seem standard and kind of messes things up a bit. If you used word please remove the header info and all that and make sure you used the standard formatting.
    (0) 
    1. Anonymous
      Hi,

      Good Blog!! I did encounter a lot of head ache thanks to the rigid data and time formats. I handled the format changing using a simple user defined function which simulates TO_Date

      SimpleDateFormat  simpleDateFormat = new SimpleDateFormat(“yyyy-MM-dd”);
      Date date = new Date(a);
      return simpleDateFormat.format(date);

      Cheers,
      Naveen

      (0) 
  2. Vishal Kulkarni
    Hi,

    Thanks for this wonderful blog. Can you also explain how to convert 24 hr to 12 hr format using this? Really having a tough time converting this!!

    Thanks,
    Vishal

    (0) 

Leave a Reply