Oracle:类似于sysdate,但只返回时间和日期 [英] Oracle: Similar to sysdate but returning only time and only date
问题描述
我理解Oracle sysdate返回当前日期和时间。这对于时间戳或日期时间列非常有用。
I understand that Oracle sysdate returns the current date AND time. That's great for timestamp or datetime columns.
现在我们假设我只有一个DATE列。在插入查询时应该使用什么关键字?
Now let's say I have a DATE only column. What keywords should I use on my insert query?
insert into myTable1(myDateOnlyColumn) values(???)
假设我只有一个TIME列。我应该在我的插入查询中使用什么关键字?
And let's say I have a TIME only column. What keywords should I use on my insert query?
insert into myTable2(myTimeOnlyColumn) values(???)
谢谢!
推荐答案
p>在Oracle中没有这样的DATE only列。 DATE数据类型存储日期和时间。
There is no such thing as a DATE only column in Oracle. The DATE datatype stores date and time.
如果您只关心日期,可以:
If you only care about the date, you can:
INSERT INTO tbl (dtCol) VALUES (TO_DATE('20110929','YYYYMMDD');
b $ b
这将时间组件留在00:00:00,您不必显示它。
This leaves the time component at 00:00:00. You don't have to display it though.
如果您只对例如:
SQL> CREATE TABLE dt (d DATE);
SQL> INSERT INTO dt VALUES (TO_DATE('1:164800','J:HH24MISS'));
1 row inserted
显示列的实际内容显示插入了日期:
Showing the actual contents of the column reveals a date was inserted:
SQL> SELECT * FROM dt;
D
--------------------
0/0/0000 4:48:00 PM
$ b b
只从列中选择时间组件,即可得到所需的输出:
Selecting only the time component from the column gives you the output you want:
SQL> SELECT TO_CHAR(d, 'HH24:MI:SS') d FROM dt;
D
--------
16:48:00
SQL>
如果你认为你只需要一个时间列,你需要确保你总是插入同一日期组件。
If you think you need only a time column, you'll want to make sure you always insert the same date component.
这篇关于Oracle:类似于sysdate,但只返回时间和日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!