Converting a textual day of month using TO_DATE, ORA-01858
From: Jack <jreid_at_stirlingonline.com>
Date: Tue, 29 Apr 2008 13:11:12 -0700 (PDT)
Message-ID: <418ab611-6793-4bea-8864-591a87c34cde@d45g2000hsc.googlegroups.com>
ERROR at line 2:
ORA-01858: a non-numeric character was found where a numeric was expected
Date: Tue, 29 Apr 2008 13:11:12 -0700 (PDT)
Message-ID: <418ab611-6793-4bea-8864-591a87c34cde@d45g2000hsc.googlegroups.com>
I am trying to solve the problem of converting a day of the month
provided as a character value ('One') into an Oracle date value:
SQL> create table t1 (
2 my_date DATE
3 );
Table created.
1 INSERT INTO t1
2* VALUES (TO_DATE('June One, 2000', 'Month Ddsp, yyyy'))
SQL> /
VALUES (TO_DATE('June One, 2000', 'Month Ddsp, yyyy'))
*
ERROR at line 2:
ORA-01858: a non-numeric character was found where a numeric was expected
It looks to me like like this is due to not having a 'direct' format model for a character value for a day of the month. Month values have the "month" format model to help with this, but I can't find anything corresponding for the day of the month.
Oracle 10.2.0.1.0
Thank you for any suggestions.
Jack Received on Tue Apr 29 2008 - 15:11:12 CDT