Re: Why does the year 2000 do this?

To expand on what Jerry said, the dual to_date conversions will have an implied to_char conversion to get the date into a character string for the second (outer) to_date. This implied conversion uses the default date format, which mostly likely is 'dd-mon-yy'. This means that your nested to date conversion is like this: 1. select to_date(to_date(strDateStart, 'MM/DD/YYYY'),'DD-MON-YYYY') into datStart from dual;

      Inner to_date is correct: would be 01-JAN-2000 00:00:00 with a mask = 'dd-mon-yyyy hh24:mi:ss'
2. select to_date({date_good_as_date},'DD-MON-YYYY') into datStart from dual;

      Inner date is automatically converted to a string with the default mask and it looks like '01-JAN-00'
3. select to_date({implied_conversion_to_char},'DD-MON-YYYY') into datStart from dual;

      Inner date (as string) converted to a '01-JAN-0000', the 2 digit year is zero filled.

HTH
    James

In article <38ED534C.597835B2_at_erols.com
> jgitomer_at_erols.com wrote:
> Sean wrote:
> > > > I have a package and then a procedure within that gets past a
date in
> > string format like : 1/1/2000
> > > > I then do a simple:
> > select to_date(to_date(strDateStart, 'MM/DD/YYYY'),
> > 'DD-MON-YYYY') into datStart from dual;
> > > > Problem is that within PL/SQL, I keep getting the error:
> > (full) year must be between -4793 and +9999 and not 0
> > > > But if I do 2001, it works PERFECTLY. Is there a problem with
the year
> > 2000 or am I doing it incorrectly?
> > > > Thanks,
> > Sean
> > > > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> > It looks like you are converting a string to a date and then
> trying to convert the date to a date. Try:
> > select to_date(strDateStart, 'MM/DD/YYYY') into datStart from >
dual;
> > --
> Jerry Gitomer

• Life is complex; it has real and imaginary parts.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Apr 07 2000 - 08:57:22 CDT