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|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|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|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|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|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

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|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|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|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>

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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