oracle Oracle查询计算当前年龄
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17422223/
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 query to calculate current age
提问by user1760986
I want to calculate current age of person from DOB(date of birth) field in Oracle table.
我想从 Oracle 表中的 DOB（出生日期）字段计算人的当前年龄。
Data type of DOB field is varchar and the is date stored in format 'DD-MON-YY'.
DOB 字段的数据类型为 varchar，日期以“DD-MON-YY”格式存储。
when I calculate current age of a person from date like 10-JAN-49the query will return age in negative. Also, I observed that if date has year 13 to 49 it gives negative result.
当我从日期计算一个人的当前年龄时，10-JAN-49查询将返回负数年龄。另外，我观察到如果日期是 13 到 49 年，它会给出负面结果。
Examples
例子
22-NOV-83 -valid result
09-FEB-58 --valid result
05-JUN-49 - Invalid result like -36
Query Executed for reference
查询执行以供参考
select round(MONTHS_BETWEEN(sysdate,to_date(dob,'DD-MON-RR'))/12)||' Yrs'
from birth
Any help is appreciated!
任何帮助表示赞赏！
回答by Ronnie
To get round the 21st century problem, just modifying @the_silk's answer slightly:
为了解决 21 世纪的问题，只需稍微修改@the_silk 的答案：
SELECT
CASE WHEN SUBSTR(dob, -2, 2) > 13
THEN FLOOR
(
MONTHS_BETWEEN
(
SYSDATE
, TO_DATE(SUBSTR(dob, 1, 7) || '19' || SUBSTR(dob, -2, 2), 'DD-MON-YYYY')
) / 12
)
ELSE
FLOOR(MONTHS_BETWEEN(sysdate,TO_DATE(dob,'DD-MON-YY'))/12)
END
FROM
birth
Please be aware though that this assumes that any date year between '00' and '13' is 21st century, so this sql should only be used if you are building a one off throwaway script, otherwise it will become out of date and invalid before long.
请注意，这假定“00”和“13”之间的任何日期年份都是 21 世纪，因此只有在构建一次性脚本时才应使用此 sql，否则它将在此之前过时且无效长。
The best solution would be to rebuild this table, converting the varchar column into a date column, as alluded to by Ben.
最好的解决方案是重建这个表，将 varchar 列转换为日期列，正如 Ben 所暗示的那样。
回答by the_slk
/*
A value between 0-49 will return a 20xx year.
A value between 50-99 will return a 19xx year.
*/
Source: http://www.techonthenet.com/oracle/functions/to_date.php
来源：http: //www.techonthenet.com/oracle/functions/to_date.php
SELECT FLOOR
(
MONTHS_BETWEEN
(
SYSDATE
, TO_DATE(SUBSTR(d_date, 1, 7) || '19' || SUBSTR(d_date, -2, 2), 'DD-MON-YYYY')
) / 12
)
FROM
(
SELECT '10-JAN-49' d_date FROM DUAL
)
-- The result: 64
回答by user2793088
SELECT MONTHS_BETWEEN( to_date(sysdate,'dd-mm-rr')
, to_date(to_date(dob,'yymmdd'),'dd-mm-rr')
) / 12 AS Years
FROM birth;
Maybe this works.
也许这有效。
回答by Pankaj
My solution would be something like this:
我的解决方案是这样的：
SELECT DATE_FORMAT(CURDATE(),'%Y') - DATE_FORMAT(dob,'%Y') as age
FROM `member_master`
having age >=30 and age <=35
回答by dinesh Kumar Yadav
SELECT year,
months,
Floor(SYSDATE - day_1) AS days
FROM (SELECT year,
months,
Add_months(To_date('30-Oct-1980', 'dd-Mon-yyyy'),
12 * year + months)
day_1
FROM (SELECT year,
Floor(Trunc(Months_between(Trunc(SYSDATE),
To_date('30-Oct-1980', 'dd-Mon-yyyy')
))
- year * 12) AS months
FROM (SELECT Floor(Trunc(Months_between(Trunc(SYSDATE),
To_date('30-Oct-1980',
'dd-Mon-yyyy'
)
)) /
12) AS year
FROM dual)));
回答by Tamim Al Manaseer
Try using YY in the Year part of your TO_DATE
尝试在 TO_DATE 的 Year 部分使用 YY
RR Like YY, but the two digits are ``rounded'' to a year in the range 1950 to 2049. Thus, 06 is considered 2006 instead of 1906
RR 与 YY 类似，但两位数字在 1950 到 2049 的范围内“四舍五入”为年份。因此，06 被认为是 2006 而不是 1906
