Re: to_char incompatibility - Mailing list pgsql-hackers

> -----Original Message-----

> On Oracle:
> 
> SQL> select to_date('31-DEC-200700:00:00', 'dd-mon-yyyy hh24:mi:ss') from
> dual;
> 
> TO_DATE('
> ---------
> 31-DEC-07
> 
> On PostgreSQL:
> 
> select to_date('31-DEC-200700:00:00', 'dd-mon-yyyy hh24:mi:ss');
>    to_date
> --------------
>  200700-12-31
> 
> Now the input value is probably a mistake.  But according to the theory
> described in the PostgreSQL documentation that to_char more or less
> ignores
> whitespace unless FX is used, I think the Oracle behavior is more correct.
> In
> addition, even if it wants to take 6 digits for the year in spite of only
> 4
> Y's, the rest of the format wouldn't match anymore.
> 
> Is anyone an Oracle format code expert who can comment on this?
> 

Oracle removes all white spaces in the date you pass in and the date format.

SQL> select to_date('31  - DEC - 2007', 'dd-mon-yyyy') from dual;

TO_DATE('
---------
31-DEC-07

SQL> select to_date('31-DEC-2007', 'dd  -      mon  -  yyyy') from dual;

TO_DATE('
---------
31-DEC-07

And then in PostgreSQL with to_timestamp or to_date:

# select to_date('31-dec-2007', 'dd   -mon  -  yyyy');
ERROR:  invalid value for MON/Mon/mon

# select to_date('31  -dec-2007', 'dd-mon-yyyy');
ERROR:  invalid value for MON/Mon/mon

I've used Oracle for years but I think PostgreSQL is actually more accurate.

I put together this function very quickly that will make it behave like
Oracle:

create or replace function fn_to_date(p_date varchar, p_format varchar)
returns timestamp as
$$
declare v_date varchar; v_format varchar; v_timestamp timestamp;
begin v_date := replace(p_date, ' ', ''); v_format := replace(p_format, ' ', ''); v_timestamp := to_timestamp(v_date,
v_format);return v_timestamp;
 
exception when others then   raise exception '%', sqlerrm;
end;
$$
language 'plpgsql' security definer;


# select fn_to_date('31  -dec-2007', 'dd-mon-yyyy');    fn_to_date
---------------------2007-12-31 00:00:00
(1 row)

# select fn_to_date('31-dec-2007', 'dd-    mon-yyyy');    fn_to_date
---------------------2007-12-31 00:00:00
(1 row)


Or with your exact example:

# select fn_to_date('31-DEC-200700:00:00', 'dd-mon-yyyy hh24:mi:ss');
    fn_to_date
---------------------2007-12-31 00:00:00
(1 row)

Jon