Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Help required with to_date function
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
![]() |
![]() |