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@gmail.com> wrote:
> 2009/6/22 Jeremy Ford <jeremford@gmail.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@sss.pgh.pa.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
>