Question on Date Time - SAP & Oracle

[Former Member]

Hi All,

Can somebody tell me how to combine the 'Date' and 'Time' values obtained from SAP and pass the same to a Oracle table as a timestamp? For example, if I have a Date value of 09/11/2008 and a Time value of 19:20:30 (lets say) from SAP, I want to pass this as a combined value of 11-Sep-2008:19:20:30 to a Oracle table. Please tell me about the datetime function(s) in xMII and any Oracle function(s) in SQL query that I need to use. Appreciate it.

Regards,

V M.

[Former Member]

Resolved successfully.

[Former Member]

Hi V M,

to store date and time in an Oracle DB, define the field as date or timestamp. In MII you define a query, e.g. Command Query with a SQL Update. To get both date and time into the Oracle field, use the SQL "TO_DATE" function as described:

[http://www.techonthenet.com/oracle/functions/to_date.php]

For example, if Param.1 contains "2008-09-11 19:20:30", use the following string in your update:

OracleField = TO_DATE( '[Param.2]', 'YYYY-MM-DD HH24:MI:SS' )

I recommend to use a standard datetime format in your DB and convert it to other datetime strings on your presentation layer, e.g. in MII. When you retrieve a datetime from Oracle, you can use the SQl "TO_CHAR" function:

[http://www.techonthenet.com/oracle/functions/to_char.php]

To get your datetime back from the DB, you can use the following statement in your FixedQuery:

TO_CHAR( MY_DB_FIELD, 'YYYY-MM-DD HH24:MI:SS' ) AS COLUMN_NAME 

Finally you can convert the datetime string in your MII BS using one of the date functions MII offers. Have a look at the MII help to find out more about the [available functions|http://help.sap.com/saphelp_xmii120/helpdata/en/45/bf7f3b692f52f5e10000000a1553f7/content.htm].

Michael

[agentry_src]

Hi Venki,

In the expression editor, you can do something along these lines:

datetoxmlformat(DateFromERP&" "&TimeFromERP, "yyyy-MM-dd HH:mm:ss")

Then use the TO_DATE function as Michael posted. You may have to play around to make sure that the DateFromERP&" "&TimeFromERP gets into the proper format, but the & will do your concatenation. You can also use a number of other of the date and string functions to accomplish the same thing. But I think this is a quick way.

Good luck,

Mike

PS. The format characters are case sensitive in the expression editor. I don't think they are for the Oracle TO_DATE function.

Edited by: Michael Appleby on Sep 11, 2008 4:11 PM