从出生日期和今天计算 Oracle 年龄
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3015431/
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 Age calculation from Date of birth and Today
提问by Andromeda
I want to calculate the current Age from Date of Birth in my Oracle function.
我想从我的 Oracle 函数中的出生日期计算当前年龄。
What I am using is (Today-Dob)/30/12, but this is not accurate as some months have 31 days.
我使用的是(Today-Dob)/30/12，但这并不准确，因为有些月份有 31 天。
I need to get the correct age with the maximum precision. How can I do that?
我需要以最大的精度获得正确的年龄。我怎样才能做到这一点？
回答by N. Gasparotto
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+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>
回答by Jeffrey Kemp
For business logic I usually find a decimal number (in years) is useful:
对于业务逻辑，我通常发现十进制数（以年为单位）很有用：
select months_between(TRUNC(sysdate),
to_date('15-Dec-2000','DD-MON-YYYY')
)/12
as age from dual;
AGE
----------
9.48924731
回答by HushMamba
SELECT
TRUNC((SYSDATE - TO_DATE(DOB, 'YYYY-MM-DD'))/ 365.25) AS AGE_TODAY FROM DUAL;
This is easy and straight to the point.
这很简单，直截了当。
回答by ealrr01
Or how about this?
或者这个怎么样？
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;
回答by user2406874
This seems considerably easier than what anyone else has suggested
这似乎比其他人建议的要容易得多
select sysdate-to_date('30-jul-1977') from dual;
回答by provisota
Age (full years) of the Person:
人的年龄（整年）：
SELECT
TRUNC(months_between(sysdate, per.DATE_OF_BIRTH) / 12) AS "Age"
FROM PD_PERSONS per
回答by Rob van Wijk
And an alternative without using any arithmetic and numbers (although there is nothing wrong with that):
还有一个不使用任何算术和数字的替代方案（尽管这没有错）：
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.
回答by Ozil
You can try
你可以试试
SELECT ROUND((SYSDATE - TO_DATE('12-MAY-16'))/365.25, 5) AS AGE from DUAL;
You can configure ROUNDto show as many decimal places as you wish.
您可以配置ROUND为显示任意数量的小数位。
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 TO_DATE.
这只是一个人为的例子。在现实世界的场景中，您不会使用TO_DATE.
However, if you have date of birth in date format, you can subtract two dates safely.
但是，如果您有日期格式的出生日期，则可以安全地减去两个日期。
回答by Soufian
Suppose that you want to have the age (number of years only, a fixed number) of someone born on June 4, 1996, execute this command :
假设您想获得出生于 的某人的年龄（仅年数，固定数字）June 4, 1996，请执行以下命令：
SELECT TRUNC(TO_NUMBER(SYSDATE - TO_DATE('04-06-1996')) / 365.25) AS AGE FROM DUAL;
Result : (Executed May 28, 2019)
结果：（2019年5月28日执行）
AGE
----------
22
Explanation :
解释 ：
SYSDATE: Get system's (OS) actual date.TO_DATE('04-06-1996'): ConvertVARCHAR(string) birthdate intoDATE(SQL type).TO_NUMBER(...): Convert a date toNUMBER(SQL type)- Devide per
365.25: To have a bissextile yearevery four years (4 * 0.25 = 1 more day). Trunc(...): Retrieve the entire part only from a number.
SYSDATE: 获取系统 (OS) 的实际日期。TO_DATE('04-06-1996')：将VARCHAR（字符串）生日转换为DATE（SQL 类型）。TO_NUMBER(...): 将日期转换为NUMBER（SQL 类型）- Devide per
365.25：每四年有一个二元年（4 * 0.25 = 1 天）。 Trunc(...)：仅从数字中检索整个部分。
回答by Hardik Vaghani
You can try below method,
你可以试试下面的方法
SELECT EXTRACT(YEAR FROM APP_SUBMITTED_DATE)-EXTRACT(YEAR FROM BIRTH_DATE) FROM SOME_TABLE;
SELECT EXTRACT(YEAR FROM APP_SUBMITTED_DATE)-EXTRACT(YEAR FROM BIRTH_DATE) FROM SOME_TABLE;
It will compare years and give age accordingly.
You can also use SYSDATEinstead of APP_SUBMITTED_DATE.
它将比较年份并相应地给出年龄。
您也可以使用SYSDATE代替APP_SUBMITTED_DATE。
Regards.
问候。
