SYSDATE 和列中的日期之间的 Oracle 时差
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33066595/
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 Time Difference between SYSDATE and Date From Column
提问by SkyvrawleR
I'm very new to Oracle. I have this problem, I got 365 days difference while these 2 dates' difference is are only one day.
我对 Oracle 很陌生。我有这个问题，我有 365 天的差异，而这两个日期的差异只有一天。
This is my SYSDATE Query Output:
这是我的 SYSDATE 查询输出：
SELECT to_date(SYSDATE,'yyyy-MM-dd') FROM LOGINRECORDS
SELECT to_date(SYSDATE,'yyyy-MM-dd') FROM LOGINRECORDS
15-OCT-11
15-OCT-11
This is my date from column Query Output:
这是我从列查询输出中的日期：
SELECT to_date(LoginDate,'yyyy-mm-dd') FROM LOGINRECORDS WHERE LoginRecordId = '1000001'
SELECT to_date(LoginDate,'yyyy-mm-dd') FROM LOGINRECORDS WHERE LoginRecordId = '1000001'
15-OCT-10
15-OCT-10
And when I run this query :
当我运行此查询时：
SELECT (to_date(SYSDATE,'yyyy-MM-dd') - to_date(LoginDate,'yyyy-MM-dd')) difference FROM LOGINRECORDS WHERE LoginRecordId = '1000001'
SELECT (to_date(SYSDATE,'yyyy-MM-dd') - to_date(LoginDate,'yyyy-MM-dd')) 差异与 LOGINRECORDS WHERE LoginRecordId = '1000001'
I got this:
我懂了：
365
365
This is my table description :
这是我的表描述：
CREATE TABLE LOGINRECORDS
(
LoginRecordId NUMBER GENERATED ALWAYS AS IDENTITY START WITH 1000000 INCREMENT BY 1,
LoginDate DATE,
patientUserId NUMBER
)
Hope you guys willing to help, Tq in advance.
希望你们愿意帮忙，提前Tq。
回答by Gordon Linoff
This expression:
这个表达：
to_date(SYSDATE, 'yyyy-MM-dd')
doesn't make sense. SYSDATEis alreadya date. So, this expression converts SYSDATEto a string, using whatever local settings are on your system. Then, it converts thatresult to a date, using the format 'yyyy-MM-dd'. For many values of the local settings, this would simply fail.
没有意义。 SYSDATE是已经一个日期。因此，该表达式SYSDATE使用系统上的任何本地设置转换为字符串。然后，它使用格式将该结果转换为日期'yyyy-MM-dd'。对于本地设置的许多值，这只会失败。
If you want the difference, then do something like this:
如果您想要差异，请执行以下操作：
SELECT (trunc(SYSDATE) - trunc(LoginDate)) as difference
FROM LOGINRECORDS
WHERE LoginRecordId = '1000001';
Note that in Oracle, the DATEdata type has a time component, hence the use of trunc(). If you know that LoginDatehas no time component, then that part does not require trunc().
请注意，在 Oracle 中，DATE数据类型具有时间组件，因此使用trunc(). 如果您知道它LoginDate没有时间组件，那么该部分不需要trunc().
