Re: Date insert error...

jjablt_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
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org