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 compare开发者_StackOverflow社区d 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.