Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Help required with to_date function

Re: Help required with to_date function

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Mon, 12 Jan 2004 22:05:23 -0800
Message-ID: <1073973847.63559@yasure>


Nagesh wrote:

> I have a column in the database called "DELIVERY_DATE",it is of type
> varchar2. The value of the date(in the string format)is stored as
> "10Jun2002 15:12:30 GMT" (double quotes not included) Now in a query I
> need to use this column in a order by clause.I cannot use the standard
> date patterns in the to_date function.So I decided to truncate the GMT
> part of it. Now it works fine, but what makes this a little more
> complicated is that some vaules will not have this delivery date value
> and hence i get this exception:
> Start server side stack trace:
>
> java.sql.SQLException: ORA-01847: day of month must be between 1 and
> last day of month
>
> at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
> at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
> at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
> at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
> ..............
>
> How do i solve this.
>
> The DB is oracle 8.1.7
>
> All help is appreciated.
> Thank You
>
> Nagesh

Change the VARCHAR2 to a real date field of the DATE or TIMESTAMP data type.

Sorry to be harsh ... but anyone using character columns to store dates deserves what they get ... problems.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)
Received on Tue Jan 13 2004 - 00:05:23 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US