Question Re: Inserting Current Date with time stamp in oracle database in Technology Q&A

Inserting Current Date with time stamp in oracle database

Former Member — Mon, 12 May 2008 04:32:03 GMT

Hi Experts,

I want to insert the current Date and time stamp in a field in the Oracle Database Table.

I am able to insert date but i am not able to insert the date with time stamp. Any Suggestions??

Thanks

Naveen

Re: Inserting Current Date with time stamp in oracle database

prateek — Mon, 12 May 2008 04:34:18 GMT

Does ur database table support the length and type of the field with timestamp? What error are u getting while trying to insert?

Regards,

Prateek

Re: Inserting Current Date with time stamp in oracle database

Former Member — Mon, 12 May 2008 04:34:59 GMT

Concatinate date with time and send it to the Oracle db table field.you can use the same function for time also like date.

Re: Inserting Current Date with time stamp in oracle database

Former Member — Mon, 12 May 2008 04:36:51 GMT

Hi Prateek,

I am getting the follwoing error.

Message processing failed. Cause: com.sap.aii.af.ra.ms.api.RecoverableException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'CCS_INTERFACE.SAP_ELEC_METER' (structure 'Statement'): java.sql.SQLException: ORA-01830: date format picture ends before converting entire input string

My Data Type at Oracle database table is of type :: date

I am using CurrentDate function in XI but it doesnt give me time stamp.

Re: Inserting Current Date with time stamp in oracle database

prateek — Mon, 12 May 2008 04:46:42 GMT

I think date format of oracle wont support the timestamp. U have to edit the type

Regards,

Prateek

Re: Inserting Current Date with time stamp in oracle database

Former Member — Mon, 12 May 2008 05:19:20 GMT

Hi Ajay,

there is a data type called "AFW_TIMESTP" which holds the timestamp in the oracle DB table. Just check whether the data type is so in the Db. An example of the value it holds in R/3 is "20080117072637".

Maybe u can check with the ABAP folks about it.

Hope it was helpful

Deepti

Re: Inserting Current Date with time stamp in oracle database

Former Member — Mon, 12 May 2008 07:04:16 GMT

Hi Prateek,

I tried with the solution in the following blog.

/people/alessandro.berta/blog/2005/10/04/datetime-datatypes-and-oracle-database

But still getting error as follows

java.sql.SQLException: ORA-01861: literal does not match format string

Any Suggestions??

Thanks

Ajay

Re: Inserting Current Date with time stamp in oracle database

prateek — Mon, 12 May 2008 07:23:25 GMT

This clearly suggests that there is datatype mismatch between the oracle field and the field that u r sending from payload

Regards,

Prateek

Re: Inserting Current Date with time stamp in oracle database

Former Member — Mon, 12 May 2008 07:48:30 GMT

Hello

May be mismatch inbetween the source and target structures

and use this

to_date(sysdate,'dd/MM/yyyy')

Re: Inserting Current Date with time stamp in oracle database

Former Member — Mon, 12 May 2008 08:09:20 GMT

Naveen,

Do you want to get current date (from sysdate) with a specific format or transform a value containing a date/time value to insert it in ORACLE ?

Usually, you insert current datetimestamp in a date field using this :

TO_DATE(sysdate,'dd/mm/yyyy hh:mi:ss')

you may have to tweak the format pattern ('dd/mm....') according to your needs

if you want to transform a date, use something like this:

TO_DATE(your_date,your_format)

but make sure your format is compliant with your date, ie

TO_DATE('31/12/2008','MM/DD/YYYY') could raise error (litteral does not match) cuz ORACLE can't recognize 31 as a month pattern

Chris