Oracle cast(timestamp as date)

Question

I see incosistency in Oracle. The inconsistensy 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 formating, is there anything else? The coding with the CAST is already done, need a really easy fix.

Thanks.

Answer 1

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.

Answer 2

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

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

Answer 3

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.