Date format

[Former Member]

Hello, there!

Have you guys ever had any problems with date formats on xMII when returning data from an Oracle 10.2 database?

Aparently, my xMII doesn't recognize the hours from my date fields and it shows them all as dd/mm/yyyy 00:00:00. Day, month and year fields are OK.

If any of you what the problem should be, please share your knowledge with me.

Thanks in advance!

[agentry_src · Accepted solution]

In your SQL Script you need to use the Oracle function TO_CHAR and if you are inserting or updating a date field, you will need to use TO_DATE to put the dates in the correct format. Samples:

TO_CHAR(<yourdatefield>, '<yourdatefieldformat>')

TO_CHAR(POStartDate, 'YYYY-MM-DD HH24:MI:SS') for example will give you dates in a 24 hour format

This will return all the datetime information. Play around with it a bit. And remember that xMII usually uses an xml format for dates if you are passing information into a transaction datetime datatype variable.

Hope this helps,

Mike

Edited by: Michael Appleby on Jul 14, 2008 7:58 PM

[Former Member · Accepted solution]

I understand it, Mike.

But, in my database, the date fields are all in Oracle date type.

I expected xMII to simply format my date fields the way I told it to do.

So, why can't it display the hours?

Thanks!

[agentry_src · Accepted solution]

Eduardo,

The query interface is configured to handle queries in a non-database specific manner. Unfortunately each of the major as well as a number of minor database vendors all use a non-SQL92 standard date format. Oracle seems to be the one that is least friendly in this respect. There are also things you can do with the structure of the data connector which can address the datatype problems. What version of Oracle are you using?

In some cases, the datatype of the field in the Oracle table is not the correct type for handling. Older versions (and newer versions of old databases) used the datatype Date which generally is the one that causes problems. The datatype Timestamp does not generally have the same problems. But you may have to play around with the Data Connector setting for date conversion. There was an SDN forum message a while back that did a better technical treatment of this problem so you may want to search in last years messages.

Hope this helps,

Mike

[Former Member · Accepted solution]

That's it, Mike!

I converted my data to timestamp format and it magically worked.

But I believe it's something I'll not be able to do when working with real, not "just-playing-around" data.

Do you know if there is something I could do to work with Oracle Date datatype?

By the way, it's Oracle 10.2.

Thanks a lot!

[agentry_src · Accepted solution]

Eduardo,

I think you will need to do some SDN searching for more specifics, but my experience has been that when the field in Oracle is defined as a Date, you end up having to do database specific conversions (TO_DATE, TO_CHAR).

One thing that does occur to me is to have a DBA create a view with the DATE/TIMESTAMP conversion built into it. But that may be a bit cumbersome to do for a lot of tables.

Good luck,

Mike

[Former Member · Accepted solution]

OK, Mike, thanks a lot for your answer!