blocks|key|527600|text|如果你只是想要年份的差异，有以下几点：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|527601|SELECT+EXTRACT(YEAR+FROM+date1)+-+EXTRACT(YEAR+FROM+date2)+FROM+mytable|code-block|syntax|javascript|527602|或者你也想要小数年？|527603|SELECT+(date1+-+date2)+/+365.242199+FROM+mytable|527604|根据谷歌的说法，365.242199是以天计算的一年。|527605|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

If you just want the difference in years, there's:

<pre><code>SELECT EXTRACT(YEAR FROM date1) - EXTRACT(YEAR FROM date2) FROM mytable
</code></pre>

Or do you want fractional years as well?

<pre><code>SELECT (date1 - date2) / 365.242199 FROM mytable
</code></pre>

365.242199 is 1 year in days, according to Google.

blocks|key|744189|text|我会使用months_between，可能会与floor结合使用|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|744190|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;|code-block|syntax|javascript|744191|floor会确保你得到四舍五入的年份。如果你想要小数部分，你显然不想使用floor。|744192|entityMap^0|4|E|N|5|0|0|0|5|10|5|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@$9|V|A|W|B|C]|$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|N|$]]

I'd use <code>months_between</code>, possibly combined with <code>floor</code>:

<pre><code>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;
</code></pre>

<code>floor</code> makes sure you get down-rounded years. If you want the fractional parts, you obviously want to not use <code>floor</code>.

blocks|key|744282|text|需要找年份的区别，假如闰年一年是366天。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|744283|我不经常在甲骨文工作，请把它改得更好。下面是我是如何做到的：|744284|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))|code-block|syntax|javascript|744285|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Need to find difference in year, if leap year the a year is of 366 days.

I dont work in oracle much, please make this better. 
Here is how I did:

<pre><code>SELECT CASE
 WHEN ( (fromisleapyear = 'Y') AND (frommonth &lt; 3))
 OR ( (toisleapyear = 'Y') AND (tomonth &gt; 2)) THEN
 datedif / 366
 ELSE
 datedif / 365
 END
 yeardifference
 FROM (SELECT datedif,
 frommonth,
 tomonth,
 CASE
 WHEN ( (MOD (fromyear, 4) = 0)
 AND (MOD (fromyear, 100) &lt;&gt; 0)
 OR (MOD (fromyear, 400) = 0)) THEN
 'Y'
 END
 fromisleapyear,
 CASE
 WHEN ( (MOD (toyear, 4) = 0) AND (MOD (toyear, 100) &lt;&gt; 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))
</code></pre>

blocks|key|527826|text|我必须实现一个year+diff函数，它的工作方式类似于sybase+datediff。在这种情况下，将计算实际年份差异，而不是四舍五入的日期差异。因此，如果有两个日期相隔一天，则年份差异可以是1(请参阅select+datediff(year,+'20141231',+'20150101'))。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|527827|如果必须以这种方式计算年份差异，则使用：|527828|EXTRACT(YEAR+FROM+date_to)+-+EXTRACT(YEAR+FROM+date_from)|code-block|syntax|javascript|527829|对于日志，(几乎)完整的datediff函数：|527830|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;";|527831|entityMap|0|LINK|mutability|MUTABLE|url|/questions/tagged/sybase|1|http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36271.1570/html/blocks/X47295.htm^0|2U|19|S|6|0|Z|8|1|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@$9|11|A|12|B|C]]|D|@$9|13|A|14|1|15]|$9|16|A|17|1|18]]|E|$]]|$1|F|3|G|5|6|7|19|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|1A|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|1B|8|@]|D|@]|E|$]]|$1|O|3|P|5|J|7|1C|8|@]|D|@]|E|$K|L]]|$1|Q|3|-4|5|6|7|1D|8|@]|D|@]|E|$]]]|R|$S|$5|T|U|V|E|$W|X]]|Y|$5|T|U|V|E|$W|Z]]]]

I had to implement a year diff function which works similarly to <a href="/questions/tagged/sybase" class="post-tag" title="show questions tagged &#39;sybase&#39;" rel="tag">sybase</a> <a href="http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36271.1570/html/blocks/X47295.htm" rel="nofollow">datediff</a>. 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 <code>select datediff(year, '20141231', '20150101')</code>). 

If the year diff has to be counted this way then use:

<pre><code>EXTRACT(YEAR FROM date_to) - EXTRACT(YEAR FROM date_from)
</code></pre>

Just for the log the (almost) complete datediff function:

<pre><code>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;";
</code></pre>

blocks|key|744491|text|对于Oracle+SQL+Developer，我能够使用下面的SQL行来计算以年为单位的差异。这是为了得到相差0到10年的年份。您也可以像其他响应中所示的情况来处理您的if。祝你编码愉快！|type|unstyled|depth|inlineStyleRanges|entityRanges|data|744492|TRUNC((MONTHS_BETWEEN(<DATE_ONE>,+<DATE_TWO>)+*+31)+/+365)+>+0+and+TRUNC((MONTHS_BETWEEN(<DATE_ONE>,+<DATE_TWO>)+*+31)+/+365)+<+10|offset|length|style|CODE|744493|entityMap^0|0|0|3M|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@$D|L|E|M|F|G]]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

For Oracle SQL Developer I was able to calculate the difference in years using the below line of SQL. This was to get Years that were within 0 to 10 years difference. You can do a case like shown in some of the other responses to handle your ifs as well. Happy Coding!
<code>TRUNC((MONTHS_BETWEEN(&lt;DATE_ONE&gt;, &lt;DATE_TWO&gt;) * 31) / 365) &gt; 0 and TRUNC((MONTHS_BETWEEN(&lt;DATE_ONE&gt;, &lt;DATE_TWO&gt;) * 31) / 365) &lt; 10</code>

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?

Thanks

Oracle date difference to get number of years

有没有办法计算日期之间的年数。不确定如何做到这一点，同时考虑到leap和什么不是。是否可以在SELECT语句中执行IF语句？谢谢

问Oracle日期差异以获取年数
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Oracle日期差异以获取年数EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Oracle日期差异以获取年数
EN