Oracle 中的日期时间比较
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16186411/
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
DateTime Comparison in Oracle
提问by noobie
I am doing comparison between several dates in my stored procedure.
我正在存储过程中的几个日期之间进行比较。
TODAY := TO_DATE(TO_CHAR(SYSDATE, 'DD-MON-YYYY') ||
' 09:00:00', 'DD-MON-YYYY HH24:MI:SS');
IF PREVIOUS_DATE < TODAY AND
TO_DATE(CURRENT_DATE, 'DD-MON-YYYY HH24:MI:SS') >= TODAY THEN
-- do something
ELSE
-- do something else
When I set CURRENT_DATE = SYSDATE, it did not get into the IFpart. Can someone tell me where did i do wrong?
当我设置时CURRENT_DATE = SYSDATE，它没有进入IF零件。有人能告诉我我哪里做错了吗？
回答by APC
"CURRENT_DATE is of type VARCHAR2 "
“CURRENT_DATE 是 VARCHAR2 类型”
Well that scuppers my first idea 8-)
好吧，这破坏了我的第一个想法 8-)
But I think the problem is in your use of CURRENT_DATE. Assuming that is the Oracle function there are two potential problems:
但我认为问题在于您使用CURRENT_DATE. 假设是oracle函数有两个潜在的问题：
- CURRENT_DATE is a DATE datatype and so has a time element. There's no point in casting it to a date.
CURRENT_DATE returns a value adjusted for the system timezone. Which is presumably the point of your IF test. But do you know what the values are?
- CURRENT_DATE 是 DATE 数据类型，因此具有时间元素。把它投射到一个日期上是没有意义的。
CURRENT_DATE 返回针对系统时区调整的值。这大概是您 IF 测试的重点。但是你知道价值观是什么吗？
But it remains a straightforward debugging task. Check your values to understand what's happening. Here is an example using DBMS_OUTPUT (AKA The Devil's Debugger) because I don't know if you're working in an environment with better tools.
但它仍然是一项简单的调试任务。检查您的价值观以了解正在发生的事情。这是一个使用 DBMS_OUTPUT（又名 The Devil's Debugger）的示例，因为我不知道您是否在具有更好工具的环境中工作。
TODAY := trunc(sysdate) + 9/24;
dbms_output.put_line('TODAY = '||to_char(today, 'DD-MON-YYYY HH24:MI:SS'));
dbms_output.put_line('PREVIOUS_DATE = '||to_char(previous_date, 'DD-MON-YYYY HH24:MI:SS'));
dbms_output.put_line('CURRENT_DATE = '||current_date);
IF PREVIOUS_DATE < TODAY AND
to_date(CURRENT_DATE, 'DD-MON-YYYY HH24:MI:SS') >= TODAY
THEN
....
Might as well simplify your code at the same time.
不妨同时简化您的代码。
Remember, to see output from DBMS_OUTPUT.PUT_LINE you need to SET SERVEROUTPUT ON for whatever client you're using.
请记住，要查看 DBMS_OUTPUT.PUT_LINE 的输出，您需要为正在使用的任何客户端设置 SERVEROUTPUT ON。
Incidentally, it is bad practice to declare variables which share the same name as Oracle built-ins. This is why it is a good idea to add a scoping prefix (l_for local, p_for parameter, etc) to our declarations. We don't have to do the full Hungarian to get a lot of benefit.
顺便说一句，声明与 Oracle 内置程序同名的变量是不好的做法。这就是为什么在我们的声明中添加作用域前缀（l_用于本地、p_用于参数等）是个好主意的原因。我们不必做完整的匈牙利语来获得很多好处。
