Oracle：类似于 sysdate 但只返回时间和日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7604167/
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: Similar to sysdate but returning only time and only date
提问by TraderJoeChicago
I understand that Oracle sysdate returns the current date AND time. That's great for timestamp or datetime columns.
我了解 Oracle sysdate 返回当前日期和时间。这对于时间戳或日期时间列非常有用。
Now let's say I have a DATE only column. What keywords should I use on my insert query?
现在假设我有一个 DATE only 列。我应该在插入查询中使用哪些关键字？
insert into myTable1(myDateOnlyColumn) values(???)
And let's say I have a TIME only column. What keywords should I use on my insert query?
假设我有一个仅 TIME 列。我应该在插入查询中使用哪些关键字？
insert into myTable2(myTimeOnlyColumn) values(???)
Thanks!
谢谢！
回答by psur
To remove time from sysdateyou can just write TRUNC(sysdate).
要删除时间，sysdate您可以只写TRUNC(sysdate).
For example:
例如：
SELECT TRUNC(SYSDATE) "TODAY" FROM DUAL;
will give you:
会给你：
TODAY
-------------------------
'2012-10-02 00:00:00'
回答by DCookie
There is no such thing as a DATE only column in Oracle. The DATE datatype stores date and time.
Oracle 中没有 DATE only 列之类的东西。DATE 数据类型存储日期和时间。
If you only care about the date, you can:
如果你只关心日期，你可以：
INSERT INTO tbl (dtCol) VALUES (TO_DATE('20110929','YYYYMMDD');
This leaves the time component at 00:00:00. You don't have to display it though.
这将时间组件留在 00:00:00。不过，您不必显示它。
If you're only interested in the time component, you still have a date stored in the column. You'll just have to handle that on output. For example:
如果您只对时间组件感兴趣，您仍然可以在列中存储一个日期。你只需要在输出上处理它。例如：
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
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.
如果您认为您只需要一个时间列，您需要确保始终插入相同的日期组件。
回答by Gorgi
select sysdate, to_date(to_char(sysdate, 'dd/mm/yyyy'),'dd/mm/yyyy') d
from dual
