Oracle 日期差异以获取年数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7943055/
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 date difference to get number of years
提问by Matt
Is there a way to calculate the number of years between dates. Not sure how to do this while accounting for leap and what not. Is it possible to do an IF statement maybe in the SELECT?
有没有办法计算日期之间的年数。不知道如何做到这一点，同时考虑到飞跃和什么不是。是否可以在 SELECT 中执行 IF 语句？
Thanks
谢谢
回答by René Nyffenegger
I'd use months_between, possibly combined with floor:
我会使用months_between，可能结合使用floor：
select floor(months_between(date '2012-10-10', date '2011-10-10') /12) from dual;
select floor(months_between(date '2012-10-9' , date '2011-10-10') /12) from dual;
floormakes sure you get down-rounded years. If you want the fractional parts, you obviously want to not use floor.
floor确保你得到四舍五入的年份。如果你想要小数部分，你显然不想使用floor.
回答by eaolson
If you just want the difference in years, there's:
如果您只想要年份的差异，则有：
SELECT EXTRACT(YEAR FROM date1) - EXTRACT(YEAR FROM date2) FROM mytable
Or do you want fractional years as well?
或者你也想要小数年？
SELECT (date1 - date2) / 365.242199 FROM mytable
365.242199 is 1 year in days, according to Google.
根据谷歌的说法，365.242199 是 1 年的天数。
回答by TrueY
I had to implement a year diff function which works similarly to sybasedatediff. In that case the real year difference is counted, not the rounded day difference. So if there are two dates separated by one day, the year difference can be 1 (see select datediff(year, '20141231', '20150101')).
我必须实现一个与sybase datediff类似的 year diff 函数。在这种情况下，计算的是实际年差，而不是四舍五入的日差。因此，如果两个日期相隔一天，则年差可以为 1（请参阅 参考资料select datediff(year, '20141231', '20150101')）。
If the year diff has to be counted this way then use:
如果必须以这种方式计算年份差异，请使用：
EXTRACT(YEAR FROM date_to) - EXTRACT(YEAR FROM date_from)
Just for the log the (almost) complete datediff function:
仅用于日志（几乎）完整的 datediff 函数：
CREATE OR REPLACE FUNCTION datediff (datepart IN VARCHAR2, date_from IN DATE, date_to IN DATE)
RETURN NUMBER
AS
diff NUMBER;
BEGIN
diff := CASE datepart
WHEN 'day' THEN TRUNC(date_to,'DD') - TRUNC(date_from, 'DD')
WHEN 'week' THEN (TRUNC(date_to,'DAY') - TRUNC(date_from, 'DAY')) / 7
WHEN 'month' THEN MONTHS_BETWEEN(TRUNC(date_to, 'MONTH'), TRUNC(date_from, 'MONTH'))
WHEN 'year' THEN EXTRACT(YEAR FROM date_to) - EXTRACT(YEAR FROM date_from)
END;
RETURN diff;
END;";
回答by user3036120
Need to find difference in year, if leap year the a year is of 366 days.
需要找出年份的差异，如果闰年一年是 366 天。
I dont work in oracle much, please make this better. Here is how I did:
我在 oracle 中工作不多，请让它变得更好。这是我的做法：
SELECT CASE
WHEN ( (fromisleapyear = 'Y') AND (frommonth < 3))
OR ( (toisleapyear = 'Y') AND (tomonth > 2)) THEN
datedif / 366
ELSE
datedif / 365
END
yeardifference
FROM (SELECT datedif,
frommonth,
tomonth,
CASE
WHEN ( (MOD (fromyear, 4) = 0)
AND (MOD (fromyear, 100) <> 0)
OR (MOD (fromyear, 400) = 0)) THEN
'Y'
END
fromisleapyear,
CASE
WHEN ( (MOD (toyear, 4) = 0) AND (MOD (toyear, 100) <> 0)
OR (MOD (toyear, 400) = 0)) THEN
'Y'
END
toisleapyear
FROM (SELECT (:todate - :fromdate) AS datedif,
TO_CHAR (:fromdate, 'YYYY') AS fromyear,
TO_CHAR (:fromdate, 'MM') AS frommonth,
TO_CHAR (:todate, 'YYYY') AS toyear,
TO_CHAR (:todate, 'MM') AS tomonth
FROM DUAL))
