blocks|key|1011574|text|SQL>+select+trunc(months_between(sysdate,dob)/12)+year,
++2+++++++++trunc(mod(months_between(sysdate,dob),12))+month,
++3+++++++++trunc(sysdate-add_months(dob,trunc(months_between(sysdate,dob)/12)*12%2Btrunc(mod(months_between(sysdate,dob),12))))+day
++4++from+(Select+to_date('15122000','DDMMYYYY')+dob+from+dual);

++++++YEAR++++++MONTH++++++++DAY
----------+----------+----------
+++++++++9++++++++++5+++++++++26

SQL>|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1011575|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>SQL&gt; select trunc(months_between(sysdate,dob)/12) year,
 2 trunc(mod(months_between(sysdate,dob),12)) month,
 3 trunc(sysdate-add_months(dob,trunc(months_between(sysdate,dob)/12)*12+trunc(mod(months_between(sysdate,dob),12)))) day
 4 from (Select to_date('15122000','DDMMYYYY') dob from dual);

 YEAR MONTH DAY
---------- ---------- ----------
 9 5 26

SQL&gt;
</code></pre>

blocks|key|1226215|text|对于业务逻辑，我通常会发现十进制数(以年为单位)很有用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1226216|select+months_between(TRUNC(sysdate),
++++++++++++++++++++++to_date('15-Dec-2000','DD-MON-YYYY')
+++++++++++++++++++++)/12
as+age+from+dual;

+++++++AGE
----------
9.48924731|code-block|syntax|javascript|1226217|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

For business logic I usually find a decimal number (in years) is useful:

<pre><code>select months_between(TRUNC(sysdate),
 to_date('15-Dec-2000','DD-MON-YYYY')
 )/12
as age from dual;

 AGE
----------
9.48924731
</code></pre>

blocks|key|1226423|text|SELECT+
TRUNC((SYSDATE+-+TO_DATE(DOB,+'YYYY-MM-DD'))/+365.25)+AS+AGE_TODAY+FROM+DUAL;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1226424|这很简单，也很直截了当。|unstyled|1226425|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>SELECT 
TRUNC((SYSDATE - TO_DATE(DOB, 'YYYY-MM-DD'))/ 365.25) AS AGE_TODAY FROM DUAL;
</code></pre>

This is easy and straight to the point.

blocks|key|1226484|text|该人的年龄(全年)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1226485|SELECT
++TRUNC(months_between(sysdate,+per.DATE_OF_BIRTH)+/+12)+AS+"Age"
FROM+PD_PERSONS+per|code-block|syntax|javascript|1226486|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Age (full years) of the Person:

<pre><code>SELECT
 TRUNC(months_between(sysdate, per.DATE_OF_BIRTH) / 12) AS "Age"
FROM PD_PERSONS per
</code></pre>

blocks|key|1011696|text|或者这样怎么样？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1011697|with+some_birthdays+as
(+
++++select+date+'1968-06-09'+d+from+dual+union+all
++++select+date+'1970-06-10'+from+dual+union+all
++++select+date+'1972-06-11'+from+dual+union+all
++++select+date+'1974-12-11'+from+dual+union+all
++++select+date+'1976-09-17'+from+dual
)
select+trunc(sysdate)+today
,+d+birth_date
,+floor(months_between(trunc(sysdate),d)/12)+age
from+some_birthdays;|code-block|syntax|javascript|1011698|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Or how about this?

<pre><code>with some_birthdays as
( 
 select date '1968-06-09' d from dual union all
 select date '1970-06-10' from dual union all
 select date '1972-06-11' from dual union all
 select date '1974-12-11' from dual union all
 select date '1976-09-17' from dual
)
select trunc(sysdate) today
, d birth_date
, floor(months_between(trunc(sysdate),d)/12) age
from some_birthdays;
</code></pre>

blocks|key|1226171|text|还有一种不使用任何算术和数字的替代方案(尽管这没有什么问题)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1226172|SQL>+with+some_birthdays+as
++2++(+select+date+'1968-06-09'+d+from+dual+union+all
++3++++select+date+'1970-06-10'+from+dual+union+all
++4++++select+date+'1972-06-11'+from+dual+union+all
++5++++select+date+'1974-12-11'+from+dual+union+all
++6++++select+date+'1976-09-17'+from+dual
++7++)
++8++select+trunc(sysdate)+today
++9+++++++,+d+birth_date
+10+++++++,+extract(year+from+numtoyminterval(months_between(trunc(sysdate),d),'month'))+age
+11++++from+some_birthdays
+12++/

TODAY+++++++++++++++BIRTH_DATE+++++++++++++++++AGE
-------------------+-------------------+----------
10-06-2010+00:00:00+09-06-1968+00:00:00+++++++++42
10-06-2010+00:00:00+10-06-1970+00:00:00+++++++++40
10-06-2010+00:00:00+11-06-1972+00:00:00+++++++++37
10-06-2010+00:00:00+11-12-1974+00:00:00+++++++++35
10-06-2010+00:00:00+17-09-1976+00:00:00+++++++++33

5+rows+selected.|code-block|syntax|javascript|1226173|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

And an alternative without using any arithmetic and numbers (although there is nothing wrong with that):

<pre><code>SQL&gt; with some_birthdays as
 2 ( select date '1968-06-09' d from dual union all
 3 select date '1970-06-10' from dual union all
 4 select date '1972-06-11' from dual union all
 5 select date '1974-12-11' from dual union all
 6 select date '1976-09-17' from dual
 7 )
 8 select trunc(sysdate) today
 9 , d birth_date
 10 , extract(year from numtoyminterval(months_between(trunc(sysdate),d),'month')) age
 11 from some_birthdays
 12 /

TODAY BIRTH_DATE AGE
------------------- ------------------- ----------
10-06-2010 00:00:00 09-06-1968 00:00:00 42
10-06-2010 00:00:00 10-06-1970 00:00:00 40
10-06-2010 00:00:00 11-06-1972 00:00:00 37
10-06-2010 00:00:00 11-12-1974 00:00:00 35
10-06-2010 00:00:00 17-09-1976 00:00:00 33

5 rows selected.
</code></pre>

blocks|key|1011743|text|这似乎比其他任何人所建议的都要容易得多。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1011744|select+sysdate-to_date('30-jul-1977')+from+dual;+|code-block|syntax|javascript|1011745|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This seems considerably easier than what anyone else has suggested

<pre><code>select sysdate-to_date('30-jul-1977') from dual; 
</code></pre>

blocks|key|1226579|text|你可以试试|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1226580|SELECT+ROUND((SYSDATE+-+TO_DATE('12-MAY-16'))/365.25,+5)+AS+AGE+from+DUAL;|code-block|syntax|javascript|1226581|您可以将ROUND配置为显示任意多个小数位。|offset|length|style|CODE|1226582|将日期放入十进制格式，如前述，有助于计算年龄组等。|1226583|这只是一个人为的例子。在实际场景中，您不会使用TO_DATE将字符串转换为日期。|1226584|但是，如果您有日期格式的出生日期，则可以安全地减去两个日期。|1226585|entityMap^0|0|0|4|5|0|0|N|7|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@$I|X|J|Y|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|Z|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|10|8|@$I|11|J|12|K|L]]|9|@]|A|$]]|$1|Q|3|R|5|6|7|13|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|T|$]]

You can try

<pre><code>SELECT ROUND((SYSDATE - TO_DATE('12-MAY-16'))/365.25, 5) AS AGE from DUAL;
</code></pre>

You can configure <code>ROUND</code> to show as many decimal places as you wish.

Placing the date in decimal format like aforementioned helps with calculations of age groups, etc.

This is just a contrived example. In real world scenarios, you wouldn't be converting strings to date using <code>TO_DATE</code>. 

However, if you have date of birth in date format, you can subtract two dates safely.

blocks|key|1011915|text|SQL>select+to_char(to_date('19-11-2017','dd-mm-yyyy'),'yyyy')+-++to_char(to_date('10-07-1986','dd-mm-yyyy'),'yyyy')+year,
to_char(to_date('19-11-2017','dd-mm-yyyy'),'mm')+-++to_char(to_date('10-07-1986','dd-mm-yyyy'),'mm')+month,
to_char(to_date('19-11-2017','dd-mm-yyyy'),'dd')+-++to_char(to_date('10-07-1986','dd-mm-yyyy'),'dd')+day+from+dual;

++++++YEAR++++++MONTH++++++++DAY
----------+----------+----------
++++++++31++++++++++4++++++++++9|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1011916|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>SQL&gt;select to_char(to_date('19-11-2017','dd-mm-yyyy'),'yyyy') - to_char(to_date('10-07-1986','dd-mm-yyyy'),'yyyy') year,
to_char(to_date('19-11-2017','dd-mm-yyyy'),'mm') - to_char(to_date('10-07-1986','dd-mm-yyyy'),'mm') month,
to_char(to_date('19-11-2017','dd-mm-yyyy'),'dd') - to_char(to_date('10-07-1986','dd-mm-yyyy'),'dd') day from dual;

 YEAR MONTH DAY
---------- ---------- ----------
 31 4 9
</code></pre>

blocks|key|1012026|text|从设计中选择trunc(to_number(sysdate+-to_date(道布)+/365.25))作为年龄|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1012027|年龄|1012028|26+25+31|1012029|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

Select trunc(to_number(sysdate - to_date(dob) /365.25)) as Age from desingh
<h2>AGE</h2>
26
25
31

blocks|key|1012056|text|++++SELECT+FLOOR(MONTHS_BETWEEN(TRUNC(SYSDATE,+'DD'),+dob)/12)
++++FROM+dual;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1012057|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code> SELECT FLOOR(MONTHS_BETWEEN(TRUNC(SYSDATE, 'DD'), dob)/12)
 FROM dual;
</code></pre>

blocks|key|1226359|text|select+(SYSDATE-道布)+dual中的/365+"Age“|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1226360|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

select (SYSDATE-DOB)/365 "Age" from dual

I want to calculate the current Age from Date of Birth in my Oracle function.

What I am using is <code>(Today-Dob)/30/12</code>, but this is not accurate as some months have 31 days. 

I need to get the correct age with the maximum precision. How can I do that?

Oracle Age calculation from Date of birth and Today

我想在我的Oracle函数中从出生日期计算当前年龄。我使用的是(Today-Dob)/30/12，但这并不准确，因为有些月份有31天。我需要以最高的精度得到正确的年龄。我该怎么做呢？

问从出生日期和今天计算Oracle年龄
EN

回答 12

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问从出生日期和今天计算Oracle年龄EN

回答 12

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问从出生日期和今天计算Oracle年龄
EN