Re: BUG #4862: different results in to_date() between 8.3.7 & 8.4.RC1

Hi Brendan.

Results as requested - Oracle 10g:

SELECT TO_DATE(' 2009 03', 'YYYYMM') AS nospace FROM dual

NOSPACE

----------------------

1/03/2009

1 row selected

SELECT TO_DATE(' 2009 03', 'YYYY MM') AS monthspace FROM dual

MONTHSPACE

----------------------

1/03/2009

1 row selected

SELECT TO_DATE(' 2009 03', ' YYYY MM') AS bothspaces FROM dual

BOTHSPACES

----------------------

1/03/2009

1 row selected

SELECT TO_DATE(' 2009 03', ' YYYY MM') AS extraspace FROM dual

EXTRASPACE

----------------------

1/03/2009

1 row selected

SELECT TO_DATE('2009 03', ' YYYY MM') AS bogusspace FROM dual

BOGUSSPACE

----------------------

1/03/2009

1 row selected

Hope this helps,
Jeremy.

On Mon, Jun 22, 2009 at 4:39 PM, Brendan Jurd <direvus(at)gmail(dot)com> wrote:

> 2009/6/22 Jeremy Ford <jeremford(at)gmail(dot)com>:
> > Oracle 9i:
> > YEAR MONTH METHOD1 METHOD2
> >
> > 2009 03 1/03/2009 1/03/2009
> >
> > Oracle 10g:
> > YEAR MONTH METHOD1 METHOD2
> >
> > 2009 03 1/03/2009 1/03/2009
> >
>
> Hi Jeremy,
>
> The query you used above might not tell us the whole story, because
> you also have calls to to_char() in there (which is where those
> leading spaces are coming from), and I have no idea whether Oracle's
> to_char() also adds those leading spaces.
>
> It would be very helpful if you'd test the following against Oracle
> and let us know what you get back, just to totally rule out any
> interference from to_char():
>
> SELECT to_date(' 2009 03', 'YYYYMM') as nospace;
> SELECT to_date(' 2009 03', 'YYYY MM') as monthspace;
> SELECT to_date(' 2009 03', ' YYYY MM') as bothspaces;
> SELECT to_date(' 2009 03', ' YYYY MM') as extraspace;
> SELECT to_date('2009 03', ' YYYY MM') as bogusspace;
>
> > On Sat, Jun 20, 2009 at 2:03 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >> My feeling about it is that we usually try to match Oracle's behavior
> >> for to_date/to_char, so the $64 question is whether Oracle allows a
> >> leading space in these same cases. Anyone have it handy to test?
>
> If it turns out that Oracle does ignore leading spaces, we might want
> to just add a special case in the input string scanner to skip over
> any whitespace at the beginning of the string before we begin
> processing in earnest.
>
> Depending on the results from the test cases I posted above, we might
> want to do something similar for the format string also.
>
> Cheers,
> BJ
>