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