SQL 如何使用 Oracle 获得以年、月和日为单位的年龄
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33343596/
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
How to get age in years,months and days using Oracle
提问by maspinu
I'm trying to print for each person its age using this format :
我正在尝试使用以下格式为其年龄的每个人打印：
E.g : 19 years , 8 months , 13 days.
例如：19 年，8 个月，13 天。
I've googled a lot and I've noticed that there is a specific function to calculate the difference between dates DATEDIFF.
我用谷歌搜索了很多，我注意到有一个特定的函数来计算日期之间的差异DATEDIFF。
However this function does not exist in SQL*Plus, so I went on trying using MONTHS_BETWEEN()and some operators.
但是这个函数在 中不存在SQL*Plus，所以我继续尝试使用MONTHS_BETWEEN()和一些运算符。
My attempt:
我的尝试：
SELECT name , ' ' ||
FLOOR(MONTHS_BETWEEN(to_date(SYSDATE),to_date(date_of_birth))/12)||' years ' ||
FLOOR(MOD(MONTHS_BETWEEN(to_date(SYSDATE),to_date(date_of_birth)),12)) || ' months ' ||
FLOOR(MOD(MOD(MONTHS_BETWEEN(to_date(SYSDATE),to_date(date_of_birth)),12),4))|| ' days ' AS "Age"
FROM persons;
My issue relies on getting the days. I don't know how should I calculate the days , using this function ('tried dividing by 4 , or 30); I'm thinking my logic is bad but I can't figure it out , any ideas ?
我的问题依赖于日子。我不知道我应该如何使用这个函数计算天数（'尝试除以 4 或 30）；我认为我的逻辑很糟糕，但我想不通，有什么想法吗？
回答by Alex Poole
Very similar to Lalit's answer, but you can get an accurate number of days without assuming 30 days per month, by using add_monthsto adjust by the total whole-month difference:
与 Lalit 的答案非常相似，但您可以通过使用add_months按整个月的总差异进行调整来获得准确的天数，而无需假设每月有 30 天：
select sysdate,
hiredate,
trunc(months_between(sysdate,hiredate) / 12) as years,
trunc(months_between(sysdate,hiredate) -
(trunc(months_between(sysdate,hiredate) / 12) * 12)) as months,
trunc(sysdate)
- add_months(hiredate, trunc(months_between(sysdate,hiredate))) as days
from emp;
SYSDATE HIREDATE YEARS MONTHS DAYS
---------- ---------- ---------- ---------- ----------
2015-10-26 1980-12-17 34 10 9
2015-10-26 1981-02-20 34 8 6
2015-10-26 1981-02-22 34 8 4
2015-10-26 1981-04-02 34 6 24
2015-10-26 1981-09-28 34 0 28
2015-10-26 1981-05-01 34 5 25
2015-10-26 1981-06-09 34 4 17
2015-10-26 1982-12-09 32 10 17
2015-10-26 1981-11-17 33 11 9
2015-10-26 1981-09-08 34 1 18
2015-10-26 1983-01-12 32 9 14
2015-10-26 1981-12-03 33 10 23
2015-10-26 1981-12-03 33 10 23
2015-10-26 1982-01-23 33 9 3
You can verify by reversing the calculation:
您可以通过反转计算来验证：
with tmp as (
select trunc(sysdate) as today,
hiredate,
trunc(months_between(sysdate,hiredate) / 12) as years,
trunc(months_between(sysdate,hiredate) -
(trunc(months_between(sysdate,hiredate) / 12) * 12)) as months,
trunc(sysdate)
- add_months(hiredate, trunc(months_between(sysdate,hiredate))) as days
from emp
)
select * from tmp
where today != add_months(hiredate, (12 * years) + months) + days;
no rows selected
回答by Lalit Kumar B
Getting the age in terms of YEARSand MONTHSis easy, but the tricky part is the the DAYS.
根据YEARS和MONTHS获得年龄很容易，但棘手的部分是DAYS。
If you can fix the days in a month, you could get the number of days in the same SQL. For example, using the standard SCOTT.EMPtable and assuming every month has 30days:
如果您可以修复一个月中的天数，您就可以获得相同 SQL 中的天数。例如，使用标准的SCOTT.EMP表并假设每个月都有30天数：
SQL> SELECT SYSDATE,
2 hiredate,
3 TRUNC(months_between(SYSDATE,hiredate)/12) years,
4 TRUNC(months_between(SYSDATE,hiredate) -
5 (TRUNC(months_between(SYSDATE,hiredate)/12)*12)) months,
6 TRUNC((months_between(SYSDATE,hiredate) -
7 TRUNC(months_between(SYSDATE,hiredate)))*30) days
8 FROM emp;
SYSDATE HIREDATE YEARS MONTHS DAYS
---------- ---------- ---------- ---------- ----------
2015-10-26 1980-12-17 34 10 9
2015-10-26 1981-02-20 34 8 6
2015-10-26 1981-02-22 34 8 4
2015-10-26 1981-04-02 34 6 23
2015-10-26 1981-09-28 34 0 28
2015-10-26 1981-05-01 34 5 24
2015-10-26 1981-06-09 34 4 17
2015-10-26 1982-12-09 32 10 17
2015-10-26 1981-11-17 33 11 9
2015-10-26 1981-09-08 34 1 18
2015-10-26 1983-01-12 32 9 14
2015-10-26 1981-12-03 33 10 22
2015-10-26 1981-12-03 33 10 22
2015-10-26 1982-01-23 33 9 3
14 rows selected.
But, be aware not every month has 30days. So, you cannot have the accuracy with number of days.
但是，请注意并非每个月都有30几天。因此，您无法获得天数的准确性。
UPDATE
更新
I missed the total whole-month difference which @Alex Poole has explained in his accepted answer. I will let this answer for future readers to understand the part that was missed about calculating the number of days.
我错过了@Alex Poole 在他接受的答案中解释的整个月的总差异。我会让这个答案让未来的读者了解计算天数时遗漏的部分。
Modify this:
修改这个：
TRUNC((months_between(SYSDATE,hiredate) -
TRUNC(months_between(SYSDATE,hiredate)))*30) days
With this:
有了这个：
TRUNC(SYSDATE) - add_months(hiredate, TRUNC(months_between(sysdate,hiredate)))
回答by Mr.DIpak SOnar
Syntax:
句法：
SELECT
CONCAT(
TIMESTAMPDIFF(YEAR, ?, NOW()),
' Years,',
TIMESTAMPDIFF(MONTH, ?, NOW()) % 12,
' Months,',
FLOOR(TIMESTAMPDIFF(DAY, ?, NOW()) % 30.4375),
' Days'
) AS age
FROM
DUAL
- Input:replace '?' with Date of Birth. For Example, '1994-07-08'
- Output:This query will return age in 'X' Years 'Y' Months and 'Z' days.
- 输入：替换“？” 与出生日期。例如，“1994-07-08”
- 输出：此查询将返回“X”年“Y”月和“Z”天的年龄。
回答by Abhi D.
Another simplified way for getting months would be-
获得月份的另一种简化方法是-
TRUNC(MOD(months_between(sysdate,hiredate),12)) AS months
