Oracle cast(timestamp as date)

I see an inconsistency in Oracle. The inconsistency is between the way INSERT timestamp data_type value into DATE data_type column works compared to the way CAST(timestamp as DATE) works.

INSERT appears to simply cut off the milliseconds out of the timestamp value while CAST rounds them up to the closest second.

Example:

create table test_timestamp_to_date 
(date_col date, timestamp_col timestamp(6));

insert into test_timestamp_to_date select 
to_timestamp('11-OCT-2009 2:23:23.793915 PM'),
to_timestamp('11-OCT-2009 2:23:23.793915 PM')
from dual;

insert into test_timestamp_to_date select 
cast(to_timestamp('11-OCT-2009 2:23:23.793915 PM') as date),
to_timestamp('11-OCT-2009 2:23:23.793915 PM')
from dual;

1* select to_char(date_col,'DD-MON-YYYY HH24:MI:SS') date_col, timestamp_col
     from test_timestamp_to_date
SQL> /

DATE_COL             TIMESTAMP_COL
-------------------- ----------------------------
11-OCT-2009 14:23:23 11-OCT-09 02.23.23.793915 PM
11-OCT-2009 14:23:24 11-OCT-09 02.23.23.793915 PM

Question

Is there any easy way to avoid the rounding of milliseconds while using CAST? And I am not talking about use of TO_CHAR, TO_DATE combination with certain formatting; is there anything else? The coding with the CAST is already done, but I need a really easy fix.

To whoever is interested. I just figured it out.

There is a bug in ORACLE CAST function that makes it to behave differently when using CAST in SQL compared to using CAST in PL/SQL.

The CAST function Erroneously ROUNDS fractionals in SQL and Correctly TRUNCATES them in PL/SQL.

As we see the PL/SQL behaves the same way as the "default" conversion (insert into date select timestamp) meaning that "default" conversion is working properly as well.

The bug is fixed in 11gR2 and there is a patch available for 10g.

SQL's CAST should (and will after the patch) TRUNCATE the fractionals instead of ROUNDING them.

Thanks.

Is there any easy way to avoid the rounding of milliseconds while using CAST?

No, the DATE datatype does not have the fractional seconds. There's no means within only that datatype to accommodate what you're asking.

上一篇: 从存储在oracle数据库中的long var char毫秒值中获取日期

下一篇: Oracle强制转换（时间戳记为日期）