Date format for an Oracle database

[Former Member]

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

[Former Member · Accepted solution]

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

[Former Member]

Problem Solved!!

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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