收录日期:2019/10/18 22:11:42 时间:2012-05-05 01:53:17 标签:sql,oracle,timestamp

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:

  1. TEMP TABLE

    create table test_timestamp_to_date 
    (date_col date, timestamp_col timestamp(6));
    
  2. INSERTS:

    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;
    
  3. RESULTS:

    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.