SQL 使用 decode 函数比较 Oracle 中的日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3619549/
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
Comparing dates in Oracle using the decode function
提问by Freddy
I need to compare two dates using the Oracle decode function to see if one is less than or equalto the other.
我需要使用 Oracle 解码函数比较两个日期，以查看一个日期是否less than or equal与另一个日期相符。
I found this article - http://www.techonthenet.com/oracle/functions/decode.php
我找到了这篇文章 - http://www.techonthenet.com/oracle/functions/decode.php
Which states (at the bottom) that the below decode function will return date2 if date1 > date2 :
其中说明（在底部）如果 date1 > date2 下面的解码函数将返回 date2 ：
decode((date1 - date2) - abs(date1 - date2), 0, date2, date1)
Would this not return date2 if date1 >= date2 ?
如果 date1 >= date2 ，这不会返回 date2 吗？
Or is it just if date1 > date2?
还是只是 date1 > date2？
Is there an easier solution?
有更简单的解决方案吗？
回答by Allan
That function will return date2 if date2 <= date1. Plugging in the values and translating to pseudo-code, you get if 0 - 0 = 0 then date2 else date1where both dates are the same.
如果 date2 <= date1，该函数将返回 date2。插入值并转换为伪代码，您会得到if 0 - 0 = 0 then date2 else date1两个日期相同的地方。
A better solution, if you're using 8i or later is to use case:
如果您使用的是 8i 或更高版本，则更好的解决方案是使用case：
SELECT CASE WHEN date1 >= date2 THEN date2 ELSE date1 END FROM Your_Table;
Since caseallows inequality operators, it's much more readable.
由于case允许不等式运算符，它更具可读性。
Or, if you want to be more succinct, you could use the function that's designed to return the lower of n values:
或者，如果您想更简洁，可以使用旨在返回 n 值中较低值的函数：
SELECT LEAST(date1, date2) FROM Your_Table;
(There is also a GREATESTfunction, which does the opposite.)
（还有一个GREATEST函数，它的作用正好相反。）
回答by Kirill Leontev
@Allan has already given you the best solution to me, but if you insist on using decodefunction, you can process the result of signfunction instead.
@Allan 已经给你给了我最好的解决方案，但是如果你坚持使用decode函数，你可以sign改为处理函数的结果。
http://www.techonthenet.com/oracle/functions/sign.php
http://www.techonthenet.com/oracle/functions/sign.php
sign(a)returns -1if a < 0, 0if a = 0and 1if a > 0. Thus, the following logic
sign(a)返回-1if a < 0、0ifa = 0和1if a > 0。于是，下面的逻辑
if date1 >= date2 then
return date1;
else
return date2;
end if;
could be rewritten using decodein the following way:
可以使用decode以下方式重写：
select decode(sign(date2-date1),
-1 /*this means date 1 > date 2*/, date1 /* return date1*/,
0 /*dates are equal */, date1 /* again, return date1*/,
/*in any other case, which is date2 > date1, return date2*/ date2)
from dual;
回答by FrustratedWithFormsDesigner
You could try the months_betweenfunction. It will calculate the number of months between two dates, as a decimal number.
你可以试试这个months_between功能。它将计算两个日期之间的月数，以十进制数表示。
select months_between(sysdate+30, sysdate ) from dual;
select months_between(sysdate+15, sysdate ) from dual;
In this example, the first paramater is greater than the second so it will return 1. The second line returns ~0.48 (when executed at about 11:30 AM on 2010-09-01) To get the actual date values:
在此示例中，第一个参数大于第二个，因此它将返回 1。第二行返回 ~0.48（在 2010-09-01 上午 11:30 左右执行时）获取实际日期值：
select case when months_between(sysdate+30, sysdate ) > 0 then sysdate+30 else sysdate end from dual;
In general:
一般来说：
case when months_between(dateA, dateB ) > 0 then dateA else dateB
Update:
更新：
After some experimentation, it seems the finest granularity of this function is Day.
经过一些实验，这个函数的最细粒度似乎是 Day。
select months_between(to_date('2010-10-16 23:59:59', 'YYYY-MM-DD HH24:MI:SS'),
to_date('2010-10-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
from dual;
...will return 0
...将返回 0
but
但
select months_between(to_date('2010-10-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),
to_date('2010-10-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
from dual;
will return 0.032258064516129.
将返回 0.032258064516129。
Some other interesting date difference/compare techniques here: http://www.orafaq.com/faq/how_does_one_get_the_time_difference_between_two_date_columns
其他一些有趣的日期差异/比较技术：http: //www.orafaq.com/faq/how_does_one_get_the_time_difference_between_two_date_columns
回答by orbfish
If you're trying to check by date - that is, every time in 1/1 is less than 1/2, and every on 1/1 is equal to every other time on 1/1, even if the Oracle DATE is greater - then you want to compare as follows:
如果您尝试按日期检查 - 也就是说，1/1 中的每个时间都小于 1/2，并且 1/1 上的每个时间都等于 1/1 上的其他时间，即使 Oracle DATE 更大- 那么你想比较如下：
TRUNC(DATE1) <= TRUNC(DATE2)
TRUNC(DATE1) <= TRUNC(DATE2)
I don't see this in the other answers, it is so basic it makes me wonder if I'm misunderstanding the question.
我在其他答案中没有看到这一点，它非常基本，让我怀疑我是否误解了这个问题。
回答by Ciurdy
This is better:
这个更好：
decode(sign(trunc(sysdate) - (trunc(sysdate))), 1, 1, -1, -1, 0 , 0)
1: date 1 > date 2
0: date 1 = date 2
-1: date 1 < date 2
回答by Michael Pakhantsov
will return date2 when date1 >= date2
当 date1 >= date2 时将返回 date2
