Oracle cast(timestamp as date)

Question

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.

Solution 1

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.

Solution 2

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.

Comments

[Serg]

Thanks for the reply However,it is kind of obvious that DATE data_type doesn't have the fractional seconds. I think my question was about IF anybody knows WHY would ORACLE do the conversion from TIMESTAMP to DATE differently when doing it "by default" (insert into date select timestamp) where the fractionals is simply dropped (even if milliseconds are 0.999999), compared to CAST function where fractionals get rounded to the the next second (if milliseconds are over 0.5 seconds) or to previous second (if milliseconds are less then 0.5 seconds). Sorry if I wasn't clear on the subject. Thanks

[Serg]

And also I wanted to know if anybody knows an easy way to avoid this rounding when using CAST Thanks.

[Serg]

Thanks for the reply However,it is kind of obvious that DATE data_type doesn't have the fractional seconds. I think my question was about IF anybody knows WHY would ORACLE do the conversion from TIMESTAMP to DATE differently when doing it "by default" (insert into date select timestamp) where the fractionals is simply dropped (even if milliseconds are 0.999999), compared to CAST function where fractionals get rounded to the the next second (if milliseconds are over 0.5 seconds) or to previous second (if milliseconds are less then 0.5 seconds). Sorry if I wasn't clear on the subject. Thanks

[Serg]

And also I wanted to know if anybody knows an easy way to avoid this rounding when using CAST Thanks.