Question Re: Date format for an Oracle database in Technology Q&A

Date format for an Oracle database

Former Member — Fri, 23 May 2008 05:17:10 GMT

Hi,

While sending data from XI to an oracle database I am getting this error: "literal string does not match the format".

what is the correct date format that an Oracle Database expects?

Please help,

Thanks n Regards

Pushpinder Kaur

Re: Date format for an Oracle database

Former Member — Fri, 23 May 2008 05:20:27 GMT

hi pls go thru the below blog

for DATE/TIME datatypes and Oracle Database

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

Note: reward points if solution found helpfull

Regards

Chandrakanth.k

Re: Date format for an Oracle database

Former Member — Fri, 23 May 2008 05:27:02 GMT

Hi Pushpinder,

while entering to an Oracle database, you should send a query not the direct field.

OracleDateField = TO_DATE( '2008-12-31','YYYY-MM-DD')

hasQuot = No

Also define an attribute hasQuot to remove the quotes which will be inserted by XI.

Construct the above query using UDF or graphical mapping concatenate.

Let me know if you need further clarification.

-Shamly

Edited by: Shamly MM on May 23, 2008 7:27 AM

Re: Date format for an Oracle database

Former Member — Fri, 23 May 2008 05:53:03 GMT

Hi,

using the to_date function I am getting the following error:

a non-numeric character was found where a numeric was expected

the data type of this field is DATE in the database. Does that makes a difference? Shall it be String?

Thanks n Regards

Pushpinder Kaur

Re: Date format for an Oracle database

Former Member — Fri, 23 May 2008 06:22:10 GMT

Hi Pushpinder,

Within the TO_DATE statement have you enclosed the input date within quotes?

Can you give a test date in the test tab, execute and then paste the result coming in the output?

Have you specified the attribute hasQuot = No ?

Edited by: Shamly MM on May 23, 2008 8:34 AM

Re: Date format for an Oracle database

Former Member — Fri, 23 May 2008 06:37:06 GMT

Hi Shamly,

I made some small changes and it is working fine now. The output is:

TO_DATE('2007-06-12 00:00:00','yyyy-mm-dd HH24:MI:SS')

Earlier i was giving mm in caps and hh mi ss in small. I changed that and it started working.

Not sure if that makes a difference.

Thanks a lot for your help!!

Thanks n Regards

Pushpinder Kaur

Re: Date format for an Oracle database

Former Member — Fri, 23 May 2008 06:53:00 GMT

Hi Pushpinder,

Please mark the thread as solved, if the issue is resolved.

Also, if you don't need the time part, you can use the below UDF, where the input is Date.

Capitals for YYYY-MM-DD do work but i have not tried the time notation.

if(Date!="")

{

String DateLeft = Date.substring(0,10);

return "TO_DATE('"DateLeft"','YYYY-MM-DD')";

}

else

return Date;

- Shamly

Re: Date format for an Oracle database

Former Member — Fri, 23 May 2008 06:57:48 GMT

Problem Solved!!