Need help in converting LOB to timestamp [message #388262] |
Tue, 24 February 2009 01:22  |
rnhullur
Messages: 3 Registered: February 2009 Location: Mumbai
|
Junior Member |
|
|
Hi all,
I am having a problem in converting a LOB ( which will have date value) to timestamp.
My LOB value will be in the format "(CLOB) 19/Feb/09 07:25 PM".
And I need to convert it into timestamp format with format 'dd-MON-yy HH:MI:SS AM'.
When I convert the LOB to char using to_char(dbms_lob.substr(oldstring, 18, 1)) where oldstring is the column name, I will get the value in the format '19/Feb/09 07:25 PM'. But after that I was not able to convert it into timestamp as I am getting the message 'ORA-01858' related to non-numeric character when it is expecting a numeric character.
Does anyone had this problem or is anyone having solution to this?
Thanks in advance..
-Raghu
|
|
|
|
|
|
|
|
|
|
|
Re: Need help in converting LOB to timestamp [message #388289 is a reply to message #388280] |
Tue, 24 February 2009 02:34  |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
rnhullur wrote on Tue, 24 February 2009 09:14 | Yes, I tried that...
|
What? That? SQL> to_timestamp
SP2-0734: unknown command beginning "to_timesta..." - rest of line ignored.
Or SQL> SELECT to_timestamp('19/Feb/09 07:25 PM', 'DD/Mon/YY HH:MI AM',
2 'nls_date_language=czech' ) FROM dual;
SELECT to_timestamp('19/Feb/09 07:25 PM', 'DD/Mon/YY HH:MI AM',
*
ERROR at line 1:
ORA-01843: not a valid month
Or SQL> SELECT to_timestamp('19/Feb/09 07:25 PM', 'DD/Mon/YY HH:MI AM',
2 'nls_date_language=english' ) FROM dual;
TO_TIMESTAMP('19/FEB/0907:25PM','DD/MON/YYHH:MIAM','NLS_DATE_LANGUAGE=ENGLI
---------------------------------------------------------------------------
19-FEB-09 07.25.00.000000000 PM
1 row selected.
Or ...?
|
|
|