Home » SQL & PL/SQL » SQL & PL/SQL » Date format
Date format [message #398563] Thu, 16 April 2009 14:35 Go to next message
Ravin_C
Messages: 38
Registered: February 2008
Location: United Kingdom
Member
Hi Gurus,

I have a column in a oracle table as Varchar2 with dates such as '1/13/2009' so on. When i try to insert this into a date column with the to_date(column_name,'MM/DD/YYYY')conversion, its throwing out with 'ORA-01858 error such as non-numeric found in numeric blah blah........'

Any ideas guys?
Re: Date format [message #398568 is a reply to message #398563] Thu, 16 April 2009 14:42 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I have a column in a oracle table as Varchar2 with dates such as '1/13/2009' so on.
BAD, Bad, bad implementation
Column should be DATE datatype

>to_date(column_name,'MM/DD/YYYY')
TO_DATE is used to convert string to date datatype.


>Any ideas guys?
You don't know how to properly use data types or conversion functions.

With Oracle characters between single quote marks are strings.
'this is a string'
&
'2009-04-16'
above is a string which can be INSERT into VARCHAR2 easily

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

[Updated on: Thu, 16 April 2009 14:43]

Report message to a moderator

Re: Date format [message #398569 is a reply to message #398563] Thu, 16 April 2009 14:42 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
This will almost certainly be because you have values in the varchar2 column that do not match the format mask.
Re: Date format [message #398572 is a reply to message #398568] Thu, 16 April 2009 14:54 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Quote:
>I have a column in a oracle table as Varchar2 with dates such as '1/13/2009' so on.
BAD, Bad, bad implementation
Column should be DATE datatype

Which is most likely why he is trying to insert the values into a DATE column.
Quote:
>to_date(column_name,'MM/DD/YYYY')
TO_DATE is used to convert string to date datatype.

Based on his post I'd say that he knows this.

Quote:

>Any ideas guys?
You don't know how to properly use data types or conversion functions.

Are you sure that it was the OP that created the column as varchar2 type? It looks to me like he is actually trying to resolve the situation.
Re: Date format [message #398574 is a reply to message #398563] Thu, 16 April 2009 15:00 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Ravin_C,
>I have a column in a oracle table

Post DDL used to CREATE TABLE you are having the problem with.
Re: Date format [message #398749 is a reply to message #398563] Fri, 17 April 2009 07:34 Go to previous message
Ravin_C
Messages: 38
Registered: February 2008
Location: United Kingdom
Member
Thanks guys, i have managed to fix it. There was a space on the begining which is why the to_date function was not working.
Previous Topic: Passing array of records as the input parameter to store procedure
Next Topic: generating table column values sequentially
Goto Forum:
  


Current Time: Sat Dec 10 15:06:12 CST 2016

Total time taken to generate the page: 0.14559 seconds