Date format for an Oracle database

[#1]

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]

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