> -----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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
