SQL Oracle 演员表（时间戳作为日期）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1712208/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle cast(timestamp as date)
提问by Serg
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.
我看到 Oracle 中的不一致。与 CAST(timestamp as DATE) 的工作方式相比，INSERT timestamp data_type 值到 DATE data_type 列的工作方式之间存在不一致。
INSERT appears to simply cut off the milliseconds out of the timestamp value while CAST rounds them up to the closest second.
INSERT 似乎只是从时间戳值中截断了毫秒，而 CAST 将它们四舍五入到最接近的秒。
Example:
例子：
TEMP TABLE
create table test_timestamp_to_date (date_col date, timestamp_col timestamp(6));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;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
温度表
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.
有什么简单的方法可以避免在使用 CAST 时舍入毫秒？我不是在谈论使用具有特定格式的 TO_CHAR、TO_DATE 组合；还有别的事吗？CAST 的编码已经完成，但我需要一个非常简单的修复。
回答by Serg
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.
ORACLE CAST 函数中存在一个错误，与在 PL/SQL 中使用 CAST 相比，在 SQL 中使用 CAST 时它的行为会有所不同。
The CAST function Erroneously ROUNDS fractionals in SQL and Correctly TRUNCATES them in PL/SQL.
CAST 函数在 SQL 中错误地舍入小数并在 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.
正如我们所看到的，PL/SQL 的行为方式与“默认”转换（插入日期选择时间戳）相同，这意味着“默认”转换也能正常工作。
The bug is fixed in 11gR2 and there is a patch available for 10g.
该错误已在 11gR2 中修复，并且有一个适用于 10g 的补丁。
SQL's CAST should (and will after the patch) TRUNCATE the fractionals instead of ROUNDING them.
SQL 的 CAST 应该（并且将在补丁之后）截断小数而不是四舍五入。
Thanks.
谢谢。
回答by OMG Ponies
Is there any easy way to avoid the rounding of milliseconds while using CAST?
有什么简单的方法可以避免在使用 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.
不，DATE 数据类型没有小数秒。仅在该数据类型中无法满足您的要求。
