从生日计算年龄-TSQL，Oracle和任何其他 [英] Calculating Age from Birthday - TSQL, Oracle, and any others

问题描述

我经常看到有人认为 DATEDIFF(YEAR，date_of_birth，GETDATE())会得出该人的当前年龄.

I frequently see people who assume that DATEDIFF(YEAR, date_of_birth, GETDATE()) will produce the current age of the person.

不幸的是，这是不正确的. DATEDIFF 函数(至少在SQL Server中)不计算两个日期之间的整年数-它计算跨越的日历边界数.

Unfortunately this is not correct. The DATEDIFF function, at least in SQL Server, does not count the number of full years between two dates - it counts the number of calendrical boundaries crossed.

也就是说，在 YEAR 参数的情况下， DATEDIFF 会计算交叉的一月一号的数量.使用 MONTH 参数，它将计算所经过的月份的1日.

That is, in the case of the YEAR parameter, DATEDIFF counts the number of 1-Januaries crossed. With the MONTH parameter, it would count the number of 1sts of the month crossed.

例如，一个出生于17年12月31日的婴儿本来已经"1岁"，而实际上他们直到18年12月31日才年满1岁，并且目前为0岁.

So for example, a baby born on 31-Dec-17 would already be "1 years old", when in fact they do not turn 1 years old until 31-Dec-18, and they are currently 0 years old.

以这种方式使用 DATEDIFF 函数等效于编写 YEAR(GETDATE())-YEAR(date_of_birth)，这当然不考虑位置该人的生日在一年之内.

The use of the DATEDIFF function in this way is equivalent to writing YEAR(GETDATE()) - YEAR(date_of_birth), which of course takes no account of where the person's birthday falls inside the year.

这里回顾了很多其他问题的好样本，似乎没有一个明确的答案，而且有很多答案是完全错误的.

Having reviewed a good sample of other questions here, there does not appear to be a definitive answer, and there are many answers that are quite wrong.

所以我的问题是，给定一个人的出生日期和一个参考日期，最简单的表达式是什么呢?

So my question is, what is the simplest expression that, given a person's date of birth and a reference date, calculates the age of a person as of that reference date?

我正在寻找以下方面的答案:

I'm looking for an answer in the following terms:

• 答案应该是内联表达式-能够被选择或合并到where子句中.

• The answer should be an inline expression - capable of being selected or incorporated into a where-clause.

答案应该是可靠的，并且没有错误的极端情况-但是，这种情况很少发生或令人难以置信.它应适用于新生婴儿和几百岁的人，应对leap年，并且理想地应能够将出生前的时期表示为负数(该人变成-出生前一天1岁).

The answer should be robust and have no erroneous corner cases - however infrequent or implausible those cases are. It should work for newborn babies and for persons that would be centuries old, should cope with leap years, and ideally should be capable of representing the period prior to birth as a minus number (with the person becoming -1 years old on the day before they were born).

答案应该是精确的数字-它不应该依赖于使用每年的平均天数或除以幻数，并舍入错误.我相信所有使用四舍五入的平凡方法，都有极端情况，因此并不稳健，但我有待纠正.

The answer should be numerically precise - it should not rely on using an average number of days per year or dividing by magic numbers, and rounding off the error. I believe all trivial approaches which use rounding, have corner cases and are therefore not robust, but I stand to be corrected.

对于那些生日低于2月29日的人，如果参考年是is年，则应将其年龄视为在2月29日递增；如果生日为on年，则应将其年龄视为递增.参考年不是a年.

For those with birthdays falling on the 29-Feb, it should treat their age as incrementing on 29-Feb if the reference year is a leap year, or it should treat their age as incrementing on 1-Mar if the reference year is not a leap year.

答案应忽略出生日期或参考日期的任何时间部分，也就是说，应假定该人出生于午夜，其年龄应在随后的生日的午夜增加.p>

The answer should disregard any time component of either the birthdate or the reference date - that is, the person should be assumed to have been born at midnight, and their age should increment at midnight on subsequent birthdays.

我对SQL Server的答案特别感兴趣，但也有兴趣在任何其他数据库平台上查看答案.

I'm interested in an answer for SQL Server particularly, but would also be interested to see answers for any other database platform.

编辑:感谢您给出的答案以及以前的问题的链接.该问题现已被标记为重复.

I appreciate the answers that have been given and the links to previous questions. This question has now been marked as a duplicate.

令我印象深刻的是，给定链接的答案是混乱的，并且以不正确的实现开头，并且对该链接的评论指的是该实现不正确-只是在我仔细阅读后才发现它具有已通过正确的实施方式进行了更新(夹在与我的问题无关的十进制年龄"之间).第三个答案也引起了相当多的反对，这也是不正确的.还有许多其他不同质量的答案.

What strikes me is that the accepted answer to the link given, is cluttered and begins with an incorrect implementation, and the comments on it refer to its implementation being incorrect - it was only after I read it carefully that I saw it had been updated with a correct implementation (sandwiched between a treatment of "decimal age" which is not related to my question). The third answer, which has also attracted a substantial number of up-votes, is also incorrect. There are also a myriad of other answers of varying quality.

我还要求提供一个答案，理想情况下，它可以以某种方式(参考日期早于出生日期)正确处理负年龄"，而链接的答案则不能.因此，尽管我的问题处理的是同一主题，但它不是重复的，因为我强加了该问题中不存在的几个其他约束，这些约束将(以一种或另一种方式)使对该较旧问题给出的所有答案无效问题.

I've also asked for an answer that, ideally, correctly handles "negative ages" in some way (where the reference date is before the birth date), and the linked answers do not. Therefore, whilst my question treats the same subject, it is not a duplicate, as I've imposed several additional constraints that are not present in that question, and which would invalidate (in one way or another) all the answers given on that older question.

说实话，鉴于有很多微妙的错误答案(正如我在第一段中提到的那样)，我认为这个问题将获得更多支持和更多关注，而不是支持3个投票.

To be honest, given how many subtly wrong answers abound (as I alluded to above in my first paragraph), I would have thought this question would get more upvotes and more interest, rather than a downvote and 3 votes to close.

推荐答案

正确答案

从给定的来源中整理而成，这是 SQL Server的正确答案(从2012年开始):

IIF(reference_date < date_of_birth, -1, CAST(CONVERT(CHAR(8), reference_date, 112) AS INT) - CAST(CONVERT(CHAR(8), date_of_birth, 112) AS INT) / 10000)

这会将日期转换为ISO日期字符串(YYYYMMDD)，将字符串强制转换为int，然后从另一个中减去-如果出生的月份和月份高于参考日期，则此减法会借用一个数字来自年份部分.然后，我们将整数除以10,000，以得出全年差异.这可以在years年间无缝运行.时间部分会在转换中自动被截断. CHAR(8)因为字符串是固定长度.

This converts a date into an ISO date string (YYYYMMDD), casts the string to and int, and then subtracts one from the other - if the month and monthday of birth are higher than the reference date, this subtraction borrows a digit from the year component. We then integer-divide by 10,000 to produce the whole-years difference. This works seamlessly across leap years. Time parts are automatically truncated in the conversion. CHAR(8) is used as the string is fixed length.

对于其他平台，我建议修改此SQL Server解决方案.对于2012年之前的SQL Server，将 IIF 替换为 CASE .

For other platforms, I would recommend this SQL Server solution be adapted. For SQL Server pre-2012, replace the IIF with a CASE.

当然，还有许多其他正确的方法，但这似乎是使用语法最经济的内联解决方案，而且我没有看到更好的方法.

There are of course a variety of other correct ways, but this appears to be the inline solution that is most economical with syntax, and I have not seen any better.

我只是想花点时间解决各种来源的错误答案，以使将来的读者受益.

I just thought I'd take a minute to address incorrect answers in various sources, for the benefit of any future readers.

1. DATEDIFF(YEAR, date_of_birth, reference_date)

正如我的问题中提到的那样，这似乎是许多尝试的第一种方法.不幸的是，它只计算日期中年份的差异，因此，出生于17年12月31日的婴儿在18年1月1日变成1岁.很好，但是不对.

As mentioned in my question, this seems to be the first approach many try. Unfortunately it only counts the difference in the year-part of the date, so that a baby born 31-Dec-17 turns 1 years old on 01-Jan-18. It's good but it's not right.

2. FLOOR(DATEDIFF(DAY, date_of_birth, reference_date) / 365.25)

这似乎是另一种常见的方法-也在我的问题的评论中给出.

This seems to be another common approach - and was also given in a comment on my question.

对于17年2月28日出生的婴儿，经过正确计算，他们在下个生日28年2月28日满1岁.两个日期之间相隔365天.365/365.25 = 0.99.落地它变为零.婴儿被视为在18年3月1日满1岁，这一天为时已晚.

For a baby born 28-Feb-17, properly calculated they turn 1 years old on 28-Feb-18 - their next birthday. 365 days elapse between the two dates. 365 / 365.25 = 0.99. Floored it becomes zero. The baby is treated as turning 1 on 1-Mar-18 - a day too late.

小数点后进一步细化每年的天数"除数是没有帮助的，因为任何除数恰好在365以上，在这种情况下将失败.很好，但是不对.

Further refining the "days per year" divisor after the decimal point does not help, as any divisor above 365 exactly, will fail in this case. It's good but it's not right.

3. DATEDIFF(HOUR, date_of_birth, reference_date) / 8766

(2)的一种变化是使用小时而不是几天.每天都有固定的小时数，因此8766/24 = 365.25.我们看到此解决方案等效于(2).很好，但是不对.

A variation on (2) is to use hours instead of days. There is a fixed number of hours in every day, therefore 8766/24 = 365.25. We see this solution is equivalent to (2). It's good but it's not right.

4. CONVERT( INT, ROUND(DATEDIFF(HOUR, date_of_birth, reference_date) / 8766.0, 0) )

另一种变化是对小时数进行四舍五入.出于相同的原因，它遭受与(3)完全相同的缺陷，但是另外，如果涉及到时间分量，则会在之前的最后30分钟内对向上进行四舍五入.>到生日.很好，但是不对.

Another variation is to use rounding on the hours. This suffers from exactly the same defect as (3) for the same reasons, but additionally if a time component is involved, then it rounds up the last 30 minutes of the day prior to the birthday. It's good but it's not right.

5. DATEPART(DAYOFYEAR, ...)

其他人探索了 DAYOFYEAR 是否可以提供解决方案.不幸的是，由于leap年，标准 DAYOFYEAR 不能始终如一地映射到一年中的特定日期(月和月日).很好，但是不对.

Others have explored whether DAYOFYEAR can provide a solution. Unfortunately, due to leap years, the standard DAYOFYEAR does not map consistently to specific dates (months and monthdays) of the year. It's good but it's not right.

6. FLOOR( MONTHS_BETWEEN(reference_date, date_of_birth) / 12 )

当生日为is年2月29日(a年)且参考日期为2月28日(非-年)时，此Oracle特定解决方案会下降，因为它将生日视为非-年2月28日.

This Oracle-specific solution falls down when the date of birth is 29-Feb (in a leap year), and the reference date is 28-Feb (in a non-leap year), in that it treats the birthday as 28-Feb in non-leap years.

实际上，这可能更接近为leap年婴儿庆祝生日的通用惯例，但是我在我的问题中明确指出，在这种情况下，为了计算一个人的年龄，生日应视为3月1日(与英国法律一致).

That actually may be closer to the common convention on when a birthday is celebrated for leap-year babies, but I've specified in my question that in such cases the birthday for the purposes of calculating a person's age should be treated as 1-Mar (which is consistent with UK law).

MONTHS_BETWEEN 还有其他一些古怪之处，这对于年龄计算而言可能是不可取的(例如，如果用于计算月数(例如，小于1岁的婴儿)，生日"是在随后的几个月中的同一个月日或之后).

The MONTHS_BETWEEN also has other quirks which might be undesirable for age calculations (for example, if it is used to calculate age as a number of months, such as for babies less than 1 years old, where the "birthday" falls on or after the same monthday in subsequent months).

这是很好的，在某些情况下可以认为是正确的！

It's good and it may be considered right in some contexts!

当然，除了此处给出的解决方案以外，还有其他方法可以实现正确的解决方案，但应注意一堆不正确的答案(还有我在这里未考虑的更多过度使用的示例)，以及任何创新或替代方案计算应经过全面测试，并与已知有效的计算进行比较.

There are of course other ways to implement a correct solution than the one given here, but heed should be taken of the litany of incorrect answers (and many more overwrought examples that I do not consider here), and any innovation or alternative calculation should be thoroughly tested and compared against one that is already known to work.

这篇关于从生日计算年龄-TSQL，Oracle和任何其他的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！