Thread: to_date() month translation

Andrei Popov (andyelf@yahoo.com) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
to_date() month translation

Long Description
When using to_date() format conversion with an arbitrary date-string, conversion mask description does not work as
expected,or as it works in most other databases where a similar function present. 

It is expected that when a date-string '12-January-2001' is fed to to_date() with mask 'DD-Month-YYYY' conversion
shouldresult in 2001-01-12::date.  See code sample 1 for actual result. 

Replacing 'Month' with 'Mmmmmmm' provides the correct result (see sample 2), but fails with '12-June-2001' (sample 3).
Blank-paddingmonth name to 6 characters works (sample 4) always but is (as severity suggests) a major annoyance. 

Sample Code
Sample 1:
---------

template1=# select to_date('12-January-2001','DD-Month-YYYY');
  to_date
------------
 0001-01-12
(1 row)

Sample 2:
---------

template1=# select to_date('12-January-2001','DD-Mmmmmmm-YYYY');
  to_date
------------
 2001-01-12
(1 row)

Sample 3:
---------

template1=# select to_date('12-June-2001','DD-Mmmmmmm-YYYY');
  to_date
------------
 0002-08-14
(1 row)

Sample 4:
---------

template1=# select to_date('12-January  -2001','DD-Month-YYYY');
  to_date
------------
 2001-01-12
(1 row)

No file was uploaded with this report

On Fri, Jun 15, 2001 at 04:51:26PM -0400, pgsql-bugs@postgresql.org wrote:

> Sample 1:
> ---------
>
> template1=# select to_date('12-January-2001','DD-Month-YYYY');
>   to_date
> ------------
>  0001-01-12
> (1 row)
>
> template1=# select to_date('12-January  -2001','DD-Month-YYYY');
>   to_date
> ------------
>  2001-01-12
> (1 row)
>
> No file was uploaded with this report


 What is bad on the PostgreSQL documentation and mailing list archives?!

The month name string is 9-chars or you must use FM (fill-mode)option:

test=# select to_char(now(), 'Month DD');
   to_char
--------------
 June      18
(1 row)

test=# select to_char(now(), 'FMMonth DD');
 to_char
---------
 June 18
(1 row)

test=# select to_date('12-January-2001','DD-FMMonth-YYYY');
  to_date
------------
 2001-01-12
(1 row)

                    Karel

--
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/

 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz