Re: Date insert error...

On Jul 23, 4:41 pm, DA Morgan <damor..._at_psoug.org> wrote:
> jja..._at_hotmail.com wrote:
> > Hello - I am getting the error "ORA-01843: not a valid month" during a
> > simple table insert.
>
> > Our NLS_DATE_FORMAT is set to DD-MM-RR, however the existing dates in
> > the tables
> > (recently converted from a Sybase database) are in a MM/DD/YYYY
> > HH:MM:SS format.
>
> > When I attempt to insert into a table with 2 date fields using SYSDATE
> > I get the error.
> > If I attempt to convert SYSDATE to a string and then back to a DATE
> > the error still occurs.
>
> > It seems that the SYSDATE is in a format of DD-MON-YY format when
> > trying to insert
> > (eg. 23-JUL-07).
>
> > This is what I am getting:
>
> > getdates VARCHAR2(21);
> > insertdate DATE;
>
> > getdates := TO_CHAR(SYSDATE, 'MM/DD/YYYY HH24:MI:SS');
> > insertdate := TO_DATE(getdates, 'MM/DD/YYYY HH24:MI:SS');
>
> > dbms_output.put_line (SYSDATE);
> > dbms_output.put_line (getdates);
> > dbms_output.put_line (insertdate);
>
> > The output is:
>
> > 23-JUL-07
> > 07/23/2007 13:10:53
> > 23-JUL-07
>
> > It seems the conversion into insertdate isn't working.
>
> > Any ideas why this might be failing?
>
> > Thanks
>
> > Jerry
>
> I don't see anything wrong with it.
>
> You are confusing the format of the output to the value in the variable.
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

In case Daniel's response was not clear to you by default a date value is converted into a the default character date representation controlled by the NLS_DATE_FORMAT parameter, normally DD-MON-YY, on output. You are responsible for formatting the output using the to_char function into whatever character format you desire.

Here is a reference for Overview of DATE Datatype http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/datatype.htm#CNCPT413

HTH -- Mark D Powell -- Received on Mon Jul 23 2007 - 19:02:11 CDT