calendrical | Fourmilab Calendar Converter as a library | Calendar library

Algorithms for the following:

JD 1099114.5

PHP: 3 Mar -1704 12:00:00 (PHP Internal convertor)

I did some more research the following is the output for the respective algorithms:

JD 1099114.5

PHP: 3 Mar 1704 B.C.E. 12:00:00 (PHP Internal convertor) FLN: 0 Mar 1703 B.C.E. 12:00:00 (Fliegel-Van Flandern) MEU: 18 Mar 1703 B.C.E 12:00:00 (Jean Meeus "Astronomical Algorithms, 2nd ed., corrected) RICH: 4 Mar 1703 B.C.E. 12:00:00 (Richards in Explanatory Supplement 3rd Ed.)

Each Algorithm has its issues and quirks about starting times etc.

Meeus assumes that there is a Gregorian year 0 (between -1 and 1), even if this is astronomically correct, it makes little sense from a calendrical perspective. Technically then all the results to convert into Gregorian dates is just wrong?

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

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.

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.

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.

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:

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

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

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

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

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

EDIT: 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.

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.