只有时间输入的Oracle TO_DATE将基于什么逻辑添加日期组件?

本文介绍了只有时间输入的Oracle TO_DATE将基于什么逻辑添加日期组件?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

在Oracle 11/12中运行此代码:

Running this code in Oracle 11/12:

select to_date('101200', 'hh24miss') from dual

将返回Oracle根据什么逻辑自动添加的DATE组件?

will return a DATE component that Oracle automatically adds based on what logic?

例如:

select to_char(to_date('101200', 'hh24miss'), 'yyyymmdd') from dual

20160701

我们看到添加的日期部分始终设置为当前月份的第一天.这种逻辑从何而来?

We see the added date component is always set to the first day of the current month. Where does this logic come from?

预先感谢

推荐答案

date数据类型的值始终具有日期和时间成分.如果您仅指定datetime值的时间部分，则日期部分默认为当前月份的第一天.

A value of date data type always has date and time components. if you specify only time portion of the datetime value as you did, the date portion defaults to the first day of the current month.

这里是其中一个地方(第7段)在记录了此行为的Oracle文档中.

Here is one of the places (7th paragraph) in the Oracle documentation where this behavior is documented.

如果您只需要使用和存储时间(没有日期部分)，则还有未记录的TIME文字和TIME数据类型(需要通过10407(datetime TIME数据类型创建)事件启用).

There is also undocumented TIME literal and TIME data type (needs to be enabled via 10407 (datetime TIME datatype creation) event) if you need to use and store just time, without date part.

这里是使用time文字和time数据类型的小示例.但同样，它是未记录且不受支持的功能.

Here is a small demonstration of using time literal and time data type. But again it's undocumented and unsupported feature.

SQL> select time '11:32:00' as res
  2    from dual;

res 
------------------------  
11.32.00.000000000 AM

您可以在不启用10407事件的情况下使用时间文字，但是为了能够定义time数据类型的列，需要启用10407事件:

You can use time literal without enabling 10407 event, but in order to be able to define a column of time data type the 10407 event needs to be enabled:

SQL> create table time_table(time_col time);
create table time_table(time_col time)
                                 *
ERROR at line 1:
ORA-00902: invalid datatype   

-- enable 10407 event 
SQL> alter session set events '10407 trace name context forever, level 1';

Session altered.

现在，我们可以创建一个列，该表的列类型为time数据类型:

Now we can create a table with a column of time data type:

SQL> create table time_table(time_col time);

Table created.


SQL> insert into time_table(time_col)
  2    values(time '11:34:00');

1 row created.

SQL> select * from time_table;

TIME_COL 
--------------- 
11.34.00 AM

SQL> alter session set events '10407 trace name context off';

Session altered.

这篇关于只有时间输入的Oracle TO_DATE将基于什么逻辑添加日期组件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

Time