oracle SYSDATE 但指定时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17888247/
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
SYSDATE but specify the time
提问by Jonathan Morningstar
I want to say the follow but substitute the date with SYSDATE but the time between is what I want to adjust. What would the syntax be?
我想说以下内容，但用 SYSDATE 替换日期，但两者之间的时间是我想要调整的。语法是什么？
where mydatefield between SYSDATE+'0001'and SYSDATE+'2359'
其中 mydatefieldSYSDATE+'0001'和SYSDATE+'2359'
...
WHERE TO_CHAR( MOPACTIVITY.MOPNOTIFICATIONSENDAT , 'yyyy-mm-dd hh24:mi' )
BETWEEN '2013-07-26 00:00:01' AND '2013-07-26 23:59:59'
;
回答by Rajesh Chamarthi
SYSDATE (or any other date column) in Oracle has the time component. So you need to strip that off and then add the hours/minutes/time condition.
Oracle 中的 SYSDATE（或任何其他日期列）具有时间组件。所以你需要去掉它，然后添加小时/分钟/时间条件。
Eg. to say current day 10:00 AM to 3:00 PM, you can say
例如。要说当天上午 10:00 到下午 3:00，您可以说
date_column between (trunc(sysdate) + 10/24) and (trunc(sysdate) + 15/24)
Oracle date arithmetic works on the day level. so, +1 will give you the next day, 1/24 will give you an hour and 10/24 will give you 10:00 AM in the current day.
Oracle 日期算术在日级别工作。所以，+1 会给你第二天，1/24 会给你一个小时，10/24 会给你当天上午 10:00。
SQL> alter session set nls_date_format = 'DD-Mon-YYYY HH:MI:SS AM';
Session altered.
1 select sysdate,
2 trunc(sysdate),
3 trunc(sysdate) + 10/24,
4 trunc(sysdate) + 15/24
5* from dual
SQL> /
SYSDATE 26-Jul-2013 06:26:07 PM
TRUNC(SYSDATE) 26-Jul-2013 12:00:00 AM
TRUNC(SYSDATE)+10/24 26-Jul-2013 10:00:00 AM
TRUNC(SYSDATE)+15/24 26-Jul-2013 03:00:00 PM
For your question, you seem to be interested between current day and next day, so you can try adding + 1 to the date directly, once you strip the time component.
对于您的问题，您似乎对当天和第二天之间感兴趣，因此您可以尝试在去除时间组件后直接将 + 1 添加到日期。
date_column >= trunc(sysdate) and
date_column < trunc(sysdate)+1
回答by Ed Gibbs
The best way to do this is to leave your MOPACTIVITY.MOPNOTIFICATIONSENDATas a DATEtype. That allows Oracle to optimize the query if there happens to be an index on the column. I'd recommend something like this:
做到这一点的最好方法是让你MOPACTIVITY.MOPNOTIFICATIONSENDAT的DATE类型。如果列上碰巧有索引，这允许 Oracle 优化查询。我会推荐这样的东西：
WHERE MOPACTIVITY.MOPNOTIFICATIONSENDAT >= TRUNC(SYSDATE)
AND MOPACTIVITY.MOPNOTIFICATIONSENDAT < TRUNC(SYSDATE) + 1
That boils down to "greater than or equal to today at midnight" and "less than tomorrow at midnight".
这归结为“大于或等于今天午夜”和“小于明天午夜”。
回答by Jasti
We can also trunc both the dates and then compare the result
我们也可以截断两个日期，然后比较结果
where TRUNC(MOPACTIVITY.MOPNOTIFICATIONSENDAT) = TRUNC(SYSDATE)
TRUNC Removes the timestamp from the dates
TRUNC 从日期中删除时间戳
