Re: to_char incompatibility

> -----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