# Calculating Age (in years) :: right -v- wrong

More years at various grindstones than I care to remember. Some interesting grindstones though: Shipbuilding, Aerospace ..... IT
Published:
Updated:
Calculating age can be accurate without much difficulty, so why use overly simplistic formula that are inaccurate?

When calculating a person's age; year, month and day-of-month all need to be compared to the current year, month and day-of-month. Attempting to do this by a simplistic formula can only be an approximation and many common methods that are proposed for this calculation are inaccurate, such as these:

, DATEDIFF(DAY, BirthDate, GETDATE()) / 365

, DATEDIFF(YEAR, BirthDate, GETDATE())

These simplistic approaches ignore the true impact of day/month when comparing to another date, and will frequently overstate age by 1 year     (the datediff(day method is 'better' but sill not great).

A reliable method is to do all 3 needed comparisons (year, month and day-of-month)  with the assistance of a case expression like so (in MS SQL Server syntax):

, YEAR(getdate()) - YEAR(BirthDate)                                          --<< year comparison

- ( CASE

WHEN MONTH(BirthDate) > MONTH(getdate())   --<< month comparison

OR ( MONTH(BirthDate) = MONTH(getdate())

AND DAY(BirthDate) > DAY(getdate())                     --<< day-of-month comparison

)

THEN 1 ELSE 0 END )

or:

,  DATEDIFF(YEAR,BirthDate, GetDate())
- ( CASE
THEN 1 ELSE 0 END
)

These can be compared to other calculation methods, here I have used a common table expression (CTE) to setup a range of birthdates

``````    /* demonstrating calculation of age against 2 unreliable methods */
;WITH
CTE AS (
SELECT
CAST('19100101' AS datetime) AS BirthDate
UNION ALL
FROM CTE
WHERE BirthDate < DATEADD(DAY, -1, GETDATE())
)
, CTE2 AS (
SELECT
BirthDate
, YEAR(getdate()) - YEAR(BirthDate)
- ( CASE
WHEN MONTH(BirthDate) > MONTH(getdate())
OR ( MONTH(BirthDate) = MONTH(getdate())
AND DAY(BirthDate) > DAY(getdate())
)
THEN 1 ELSE 0 END )                AS Reliable_Age1
, DATEDIFF(YEAR,BirthDate, GetDate())
- ( CASE
THEN 1 ELSE 0 END
)                                      AS Reliable_Age2
, DATEDIFF(DAY, BirthDate, GETDATE()) / 365 AS Unreliable_Age_1
, DATEDIFF(YEAR, BirthDate, GETDATE())      AS Unreliable_Age_2
FROM CTE
)
SELECT
convert(varchar(10), getdate(), 121)      AS Today
, count(BirthDate)                          AS counter
, convert(varchar(10), min(BirthDate), 121) AS min_BDT
, convert(varchar(10), max(BirthDate), 121) AS max_BDT
, Reliable_Age1
, Reliable_Age2
, Unreliable_Age_1
, Unreliable_Age_2
FROM CTE2
WHERE Reliable_Age <> Unreliable_Age_1
OR Reliable_Age <> Unreliable_Age_2
GROUP BY
, Reliable_Age1
, Reliable_Age2
, Unreliable_Age_1
, Unreliable_Age_2
ORDER BY
min_BDT

OPTION (MAXRECURSION 0)
``````

and the result table of that is:

notice how frequently the last 2 columns disagree with the reliable calculations

``````|       |            |         |            |            |   Reliable  |   Unreliable  |
| row   |   Today    | counter |  min_BDT   |  max_BDT   | Age1 | Age2 | Age_1 | Age_2 |
|-------|------------|---------|------------|------------|------|------|-------|-------|
|     1 | 2018-10-05 |      27 | 1910-10-06 | 1910-11-01 |  107 |  107 |   108 |   108 |
|     2 | 2018-10-05 |      60 | 1910-11-02 | 1910-12-31 |  107 |  107 |   107 |   108 |
|     3 | 2018-10-05 |      27 | 1911-10-06 | 1911-11-01 |  106 |  106 |   107 |   107 |
|     4 | 2018-10-05 |      60 | 1911-11-02 | 1911-12-31 |  106 |  106 |   106 |   107 |
|     5 | 2018-10-05 |      26 | 1912-10-06 | 1912-10-31 |  105 |  105 |   106 |   106 |
|     6 | 2018-10-05 |      61 | 1912-11-01 | 1912-12-31 |  105 |  105 |   105 |   106 |
|     7 | 2018-10-05 |      26 | 1913-10-06 | 1913-10-31 |  104 |  104 |   105 |   105 |
|     8 | 2018-10-05 |      61 | 1913-11-01 | 1913-12-31 |  104 |  104 |   104 |   105 |
|     9 | 2018-10-05 |      26 | 1914-10-06 | 1914-10-31 |  103 |  103 |   104 |   104 |
|    10 | 2018-10-05 |      61 | 1914-11-01 | 1914-12-31 |  103 |  103 |   103 |   104 |
|    11 | 2018-10-05 |      26 | 1915-10-06 | 1915-10-31 |  102 |  102 |   103 |   103 |
|    12 | 2018-10-05 |      61 | 1915-11-01 | 1915-12-31 |  102 |  102 |   102 |   103 |
|    13 | 2018-10-05 |      25 | 1916-10-06 | 1916-10-30 |  101 |  101 |   102 |   102 |
|    14 | 2018-10-05 |      62 | 1916-10-31 | 1916-12-31 |  101 |  101 |   101 |   102 |
|    15 | 2018-10-05 |      25 | 1917-10-06 | 1917-10-30 |  100 |  100 |   101 |   101 |
|    16 | 2018-10-05 |      62 | 1917-10-31 | 1917-12-31 |  100 |  100 |   100 |   101 |
|    17 | 2018-10-05 |      25 | 1918-10-06 | 1918-10-30 |   99 |   99 |   100 |   100 |
|    18 | 2018-10-05 |      62 | 1918-10-31 | 1918-12-31 |   99 |   99 |    99 |   100 |
|    19 | 2018-10-05 |      25 | 1919-10-06 | 1919-10-30 |   98 |   98 |    99 |    99 |
|    20 | 2018-10-05 |      62 | 1919-10-31 | 1919-12-31 |   98 |   98 |    98 |    99 |
|    21 | 2018-10-05 |      24 | 1920-10-06 | 1920-10-29 |   97 |   97 |    98 |    98 |
|    22 | 2018-10-05 |      63 | 1920-10-30 | 1920-12-31 |   97 |   97 |    97 |    98 |
|    23 | 2018-10-05 |      24 | 1921-10-06 | 1921-10-29 |   96 |   96 |    97 |    97 |
|    24 | 2018-10-05 |      63 | 1921-10-30 | 1921-12-31 |   96 |   96 |    96 |    97 |
|    25 | 2018-10-05 |      24 | 1922-10-06 | 1922-10-29 |   95 |   95 |    96 |    96 |
|    26 | 2018-10-05 |      63 | 1922-10-30 | 1922-12-31 |   95 |   95 |    95 |    96 |
|    27 | 2018-10-05 |      24 | 1923-10-06 | 1923-10-29 |   94 |   94 |    95 |    95 |
|    28 | 2018-10-05 |      63 | 1923-10-30 | 1923-12-31 |   94 |   94 |    94 |    95 |
|    29 | 2018-10-05 |      23 | 1924-10-06 | 1924-10-28 |   93 |   93 |    94 |    94 |
|    30 | 2018-10-05 |      64 | 1924-10-29 | 1924-12-31 |   93 |   93 |    93 |    94 |
|    31 | 2018-10-05 |      23 | 1925-10-06 | 1925-10-28 |   92 |   92 |    93 |    93 |
|    32 | 2018-10-05 |      64 | 1925-10-29 | 1925-12-31 |   92 |   92 |    92 |    93 |
|    33 | 2018-10-05 |      23 | 1926-10-06 | 1926-10-28 |   91 |   91 |    92 |    92 |
|    34 | 2018-10-05 |      64 | 1926-10-29 | 1926-12-31 |   91 |   91 |    91 |    92 |
|    35 | 2018-10-05 |      23 | 1927-10-06 | 1927-10-28 |   90 |   90 |    91 |    91 |
|    36 | 2018-10-05 |      64 | 1927-10-29 | 1927-12-31 |   90 |   90 |    90 |    91 |
|    37 | 2018-10-05 |      22 | 1928-10-06 | 1928-10-27 |   89 |   89 |    90 |    90 |
|    38 | 2018-10-05 |      65 | 1928-10-28 | 1928-12-31 |   89 |   89 |    89 |    90 |
|    39 | 2018-10-05 |      22 | 1929-10-06 | 1929-10-27 |   88 |   88 |    89 |    89 |
|    40 | 2018-10-05 |      65 | 1929-10-28 | 1929-12-31 |   88 |   88 |    88 |    89 |
|    41 | 2018-10-05 |      22 | 1930-10-06 | 1930-10-27 |   87 |   87 |    88 |    88 |
|    42 | 2018-10-05 |      65 | 1930-10-28 | 1930-12-31 |   87 |   87 |    87 |    88 |
|    43 | 2018-10-05 |      22 | 1931-10-06 | 1931-10-27 |   86 |   86 |    87 |    87 |
|    44 | 2018-10-05 |      65 | 1931-10-28 | 1931-12-31 |   86 |   86 |    86 |    87 |
|    45 | 2018-10-05 |      21 | 1932-10-06 | 1932-10-26 |   85 |   85 |    86 |    86 |
|    46 | 2018-10-05 |      66 | 1932-10-27 | 1932-12-31 |   85 |   85 |    85 |    86 |
|    47 | 2018-10-05 |      21 | 1933-10-06 | 1933-10-26 |   84 |   84 |    85 |    85 |
|    48 | 2018-10-05 |      66 | 1933-10-27 | 1933-12-31 |   84 |   84 |    84 |    85 |
|    49 | 2018-10-05 |      21 | 1934-10-06 | 1934-10-26 |   83 |   83 |    84 |    84 |
|    50 | 2018-10-05 |      66 | 1934-10-27 | 1934-12-31 |   83 |   83 |    83 |    84 |
|    51 | 2018-10-05 |      21 | 1935-10-06 | 1935-10-26 |   82 |   82 |    83 |    83 |
|    52 | 2018-10-05 |      66 | 1935-10-27 | 1935-12-31 |   82 |   82 |    82 |    83 |
|    53 | 2018-10-05 |      20 | 1936-10-06 | 1936-10-25 |   81 |   81 |    82 |    82 |
|    54 | 2018-10-05 |      67 | 1936-10-26 | 1936-12-31 |   81 |   81 |    81 |    82 |
|    55 | 2018-10-05 |      20 | 1937-10-06 | 1937-10-25 |   80 |   80 |    81 |    81 |
|    56 | 2018-10-05 |      67 | 1937-10-26 | 1937-12-31 |   80 |   80 |    80 |    81 |
|    57 | 2018-10-05 |      20 | 1938-10-06 | 1938-10-25 |   79 |   79 |    80 |    80 |
|    58 | 2018-10-05 |      67 | 1938-10-26 | 1938-12-31 |   79 |   79 |    79 |    80 |
|    59 | 2018-10-05 |      20 | 1939-10-06 | 1939-10-25 |   78 |   78 |    79 |    79 |
|    60 | 2018-10-05 |      67 | 1939-10-26 | 1939-12-31 |   78 |   78 |    78 |    79 |
|    61 | 2018-10-05 |      19 | 1940-10-06 | 1940-10-24 |   77 |   77 |    78 |    78 |
|    62 | 2018-10-05 |      68 | 1940-10-25 | 1940-12-31 |   77 |   77 |    77 |    78 |
|    63 | 2018-10-05 |      19 | 1941-10-06 | 1941-10-24 |   76 |   76 |    77 |    77 |
|    64 | 2018-10-05 |      68 | 1941-10-25 | 1941-12-31 |   76 |   76 |    76 |    77 |
|    65 | 2018-10-05 |      19 | 1942-10-06 | 1942-10-24 |   75 |   75 |    76 |    76 |
|    66 | 2018-10-05 |      68 | 1942-10-25 | 1942-12-31 |   75 |   75 |    75 |    76 |
|    67 | 2018-10-05 |      19 | 1943-10-06 | 1943-10-24 |   74 |   74 |    75 |    75 |
|    68 | 2018-10-05 |      68 | 1943-10-25 | 1943-12-31 |   74 |   74 |    74 |    75 |
|    69 | 2018-10-05 |      18 | 1944-10-06 | 1944-10-23 |   73 |   73 |    74 |    74 |
|    70 | 2018-10-05 |      69 | 1944-10-24 | 1944-12-31 |   73 |   73 |    73 |    74 |
|    71 | 2018-10-05 |      18 | 1945-10-06 | 1945-10-23 |   72 |   72 |    73 |    73 |
|    72 | 2018-10-05 |      69 | 1945-10-24 | 1945-12-31 |   72 |   72 |    72 |    73 |
|    73 | 2018-10-05 |      18 | 1946-10-06 | 1946-10-23 |   71 |   71 |    72 |    72 |
|    74 | 2018-10-05 |      69 | 1946-10-24 | 1946-12-31 |   71 |   71 |    71 |    72 |
|    75 | 2018-10-05 |      18 | 1947-10-06 | 1947-10-23 |   70 |   70 |    71 |    71 |
|    76 | 2018-10-05 |      69 | 1947-10-24 | 1947-12-31 |   70 |   70 |    70 |    71 |
|    77 | 2018-10-05 |      17 | 1948-10-06 | 1948-10-22 |   69 |   69 |    70 |    70 |
|    78 | 2018-10-05 |      70 | 1948-10-23 | 1948-12-31 |   69 |   69 |    69 |    70 |
|    79 | 2018-10-05 |      17 | 1949-10-06 | 1949-10-22 |   68 |   68 |    69 |    69 |
|    80 | 2018-10-05 |      70 | 1949-10-23 | 1949-12-31 |   68 |   68 |    68 |    69 |
|    81 | 2018-10-05 |      17 | 1950-10-06 | 1950-10-22 |   67 |   67 |    68 |    68 |
|    82 | 2018-10-05 |      70 | 1950-10-23 | 1950-12-31 |   67 |   67 |    67 |    68 |
|    83 | 2018-10-05 |      17 | 1951-10-06 | 1951-10-22 |   66 |   66 |    67 |    67 |
|    84 | 2018-10-05 |      70 | 1951-10-23 | 1951-12-31 |   66 |   66 |    66 |    67 |
|    85 | 2018-10-05 |      16 | 1952-10-06 | 1952-10-21 |   65 |   65 |    66 |    66 |
|    86 | 2018-10-05 |      71 | 1952-10-22 | 1952-12-31 |   65 |   65 |    65 |    66 |
|    87 | 2018-10-05 |      16 | 1953-10-06 | 1953-10-21 |   64 |   64 |    65 |    65 |
|    88 | 2018-10-05 |      71 | 1953-10-22 | 1953-12-31 |   64 |   64 |    64 |    65 |
|    89 | 2018-10-05 |      16 | 1954-10-06 | 1954-10-21 |   63 |   63 |    64 |    64 |
|    90 | 2018-10-05 |      71 | 1954-10-22 | 1954-12-31 |   63 |   63 |    63 |    64 |
|    91 | 2018-10-05 |      16 | 1955-10-06 | 1955-10-21 |   62 |   62 |    63 |    63 |
|    92 | 2018-10-05 |      71 | 1955-10-22 | 1955-12-31 |   62 |   62 |    62 |    63 |
|    93 | 2018-10-05 |      15 | 1956-10-06 | 1956-10-20 |   61 |   61 |    62 |    62 |
|    94 | 2018-10-05 |      72 | 1956-10-21 | 1956-12-31 |   61 |   61 |    61 |    62 |
|    95 | 2018-10-05 |      15 | 1957-10-06 | 1957-10-20 |   60 |   60 |    61 |    61 |
|    96 | 2018-10-05 |      72 | 1957-10-21 | 1957-12-31 |   60 |   60 |    60 |    61 |
|    97 | 2018-10-05 |      15 | 1958-10-06 | 1958-10-20 |   59 |   59 |    60 |    60 |
|    98 | 2018-10-05 |      72 | 1958-10-21 | 1958-12-31 |   59 |   59 |    59 |    60 |
|    99 | 2018-10-05 |      15 | 1959-10-06 | 1959-10-20 |   58 |   58 |    59 |    59 |
|   100 | 2018-10-05 |      72 | 1959-10-21 | 1959-12-31 |   58 |   58 |    58 |    59 |
|   101 | 2018-10-05 |      14 | 1960-10-06 | 1960-10-19 |   57 |   57 |    58 |    58 |
|   102 | 2018-10-05 |      73 | 1960-10-20 | 1960-12-31 |   57 |   57 |    57 |    58 |
|   103 | 2018-10-05 |      14 | 1961-10-06 | 1961-10-19 |   56 |   56 |    57 |    57 |
|   104 | 2018-10-05 |      73 | 1961-10-20 | 1961-12-31 |   56 |   56 |    56 |    57 |
|   105 | 2018-10-05 |      14 | 1962-10-06 | 1962-10-19 |   55 |   55 |    56 |    56 |
|   106 | 2018-10-05 |      73 | 1962-10-20 | 1962-12-31 |   55 |   55 |    55 |    56 |
|   107 | 2018-10-05 |      14 | 1963-10-06 | 1963-10-19 |   54 |   54 |    55 |    55 |
|   108 | 2018-10-05 |      73 | 1963-10-20 | 1963-12-31 |   54 |   54 |    54 |    55 |
|   109 | 2018-10-05 |      13 | 1964-10-06 | 1964-10-18 |   53 |   53 |    54 |    54 |
|   110 | 2018-10-05 |      74 | 1964-10-19 | 1964-12-31 |   53 |   53 |    53 |    54 |
|   111 | 2018-10-05 |      13 | 1965-10-06 | 1965-10-18 |   52 |   52 |    53 |    53 |
|   112 | 2018-10-05 |      74 | 1965-10-19 | 1965-12-31 |   52 |   52 |    52 |    53 |
|   113 | 2018-10-05 |      13 | 1966-10-06 | 1966-10-18 |   51 |   51 |    52 |    52 |
|   114 | 2018-10-05 |      74 | 1966-10-19 | 1966-12-31 |   51 |   51 |    51 |    52 |
|   115 | 2018-10-05 |      13 | 1967-10-06 | 1967-10-18 |   50 |   50 |    51 |    51 |
|   116 | 2018-10-05 |      74 | 1967-10-19 | 1967-12-31 |   50 |   50 |    50 |    51 |
|   117 | 2018-10-05 |      12 | 1968-10-06 | 1968-10-17 |   49 |   49 |    50 |    50 |
|   118 | 2018-10-05 |      75 | 1968-10-18 | 1968-12-31 |   49 |   49 |    49 |    50 |
|   119 | 2018-10-05 |      12 | 1969-10-06 | 1969-10-17 |   48 |   48 |    49 |    49 |
|   120 | 2018-10-05 |      75 | 1969-10-18 | 1969-12-31 |   48 |   48 |    48 |    49 |
|   121 | 2018-10-05 |      12 | 1970-10-06 | 1970-10-17 |   47 |   47 |    48 |    48 |
|   122 | 2018-10-05 |      75 | 1970-10-18 | 1970-12-31 |   47 |   47 |    47 |    48 |
|   123 | 2018-10-05 |      12 | 1971-10-06 | 1971-10-17 |   46 |   46 |    47 |    47 |
|   124 | 2018-10-05 |      75 | 1971-10-18 | 1971-12-31 |   46 |   46 |    46 |    47 |
|   125 | 2018-10-05 |      11 | 1972-10-06 | 1972-10-16 |   45 |   45 |    46 |    46 |
|   126 | 2018-10-05 |      76 | 1972-10-17 | 1972-12-31 |   45 |   45 |    45 |    46 |
|   127 | 2018-10-05 |      11 | 1973-10-06 | 1973-10-16 |   44 |   44 |    45 |    45 |
|   128 | 2018-10-05 |      76 | 1973-10-17 | 1973-12-31 |   44 |   44 |    44 |    45 |
|   129 | 2018-10-05 |      11 | 1974-10-06 | 1974-10-16 |   43 |   43 |    44 |    44 |
|   130 | 2018-10-05 |      76 | 1974-10-17 | 1974-12-31 |   43 |   43 |    43 |    44 |
|   131 | 2018-10-05 |      11 | 1975-10-06 | 1975-10-16 |   42 |   42 |    43 |    43 |
|   132 | 2018-10-05 |      76 | 1975-10-17 | 1975-12-31 |   42 |   42 |    42 |    43 |
|   133 | 2018-10-05 |      10 | 1976-10-06 | 1976-10-15 |   41 |   41 |    42 |    42 |
|   134 | 2018-10-05 |      77 | 1976-10-16 | 1976-12-31 |   41 |   41 |    41 |    42 |
|   135 | 2018-10-05 |      10 | 1977-10-06 | 1977-10-15 |   40 |   40 |    41 |    41 |
|   136 | 2018-10-05 |      77 | 1977-10-16 | 1977-12-31 |   40 |   40 |    40 |    41 |
|   137 | 2018-10-05 |      10 | 1978-10-06 | 1978-10-15 |   39 |   39 |    40 |    40 |
|   138 | 2018-10-05 |      77 | 1978-10-16 | 1978-12-31 |   39 |   39 |    39 |    40 |
|   139 | 2018-10-05 |      10 | 1979-10-06 | 1979-10-15 |   38 |   38 |    39 |    39 |
|   140 | 2018-10-05 |      77 | 1979-10-16 | 1979-12-31 |   38 |   38 |    38 |    39 |
|   141 | 2018-10-05 |       9 | 1980-10-06 | 1980-10-14 |   37 |   37 |    38 |    38 |
|   142 | 2018-10-05 |      78 | 1980-10-15 | 1980-12-31 |   37 |   37 |    37 |    38 |
|   143 | 2018-10-05 |       9 | 1981-10-06 | 1981-10-14 |   36 |   36 |    37 |    37 |
|   144 | 2018-10-05 |      78 | 1981-10-15 | 1981-12-31 |   36 |   36 |    36 |    37 |
|   145 | 2018-10-05 |       9 | 1982-10-06 | 1982-10-14 |   35 |   35 |    36 |    36 |
|   146 | 2018-10-05 |      78 | 1982-10-15 | 1982-12-31 |   35 |   35 |    35 |    36 |
|   147 | 2018-10-05 |       9 | 1983-10-06 | 1983-10-14 |   34 |   34 |    35 |    35 |
|   148 | 2018-10-05 |      78 | 1983-10-15 | 1983-12-31 |   34 |   34 |    34 |    35 |
|   149 | 2018-10-05 |       8 | 1984-10-06 | 1984-10-13 |   33 |   33 |    34 |    34 |
|   150 | 2018-10-05 |      79 | 1984-10-14 | 1984-12-31 |   33 |   33 |    33 |    34 |
|   151 | 2018-10-05 |       8 | 1985-10-06 | 1985-10-13 |   32 |   32 |    33 |    33 |
|   152 | 2018-10-05 |      79 | 1985-10-14 | 1985-12-31 |   32 |   32 |    32 |    33 |
|   153 | 2018-10-05 |       8 | 1986-10-06 | 1986-10-13 |   31 |   31 |    32 |    32 |
|   154 | 2018-10-05 |      79 | 1986-10-14 | 1986-12-31 |   31 |   31 |    31 |    32 |
|   155 | 2018-10-05 |       8 | 1987-10-06 | 1987-10-13 |   30 |   30 |    31 |    31 |
|   156 | 2018-10-05 |      79 | 1987-10-14 | 1987-12-31 |   30 |   30 |    30 |    31 |
|   157 | 2018-10-05 |       7 | 1988-10-06 | 1988-10-12 |   29 |   29 |    30 |    30 |
|   158 | 2018-10-05 |      80 | 1988-10-13 | 1988-12-31 |   29 |   29 |    29 |    30 |
|   159 | 2018-10-05 |       7 | 1989-10-06 | 1989-10-12 |   28 |   28 |    29 |    29 |
|   160 | 2018-10-05 |      80 | 1989-10-13 | 1989-12-31 |   28 |   28 |    28 |    29 |
|   161 | 2018-10-05 |       7 | 1990-10-06 | 1990-10-12 |   27 |   27 |    28 |    28 |
|   162 | 2018-10-05 |      80 | 1990-10-13 | 1990-12-31 |   27 |   27 |    27 |    28 |
|   163 | 2018-10-05 |       7 | 1991-10-06 | 1991-10-12 |   26 |   26 |    27 |    27 |
|   164 | 2018-10-05 |      80 | 1991-10-13 | 1991-12-31 |   26 |   26 |    26 |    27 |
|   165 | 2018-10-05 |       6 | 1992-10-06 | 1992-10-11 |   25 |   25 |    26 |    26 |
|   166 | 2018-10-05 |      81 | 1992-10-12 | 1992-12-31 |   25 |   25 |    25 |    26 |
|   167 | 2018-10-05 |       6 | 1993-10-06 | 1993-10-11 |   24 |   24 |    25 |    25 |
|   168 | 2018-10-05 |      81 | 1993-10-12 | 1993-12-31 |   24 |   24 |    24 |    25 |
|   169 | 2018-10-05 |       6 | 1994-10-06 | 1994-10-11 |   23 |   23 |    24 |    24 |
|   170 | 2018-10-05 |      81 | 1994-10-12 | 1994-12-31 |   23 |   23 |    23 |    24 |
|   171 | 2018-10-05 |       6 | 1995-10-06 | 1995-10-11 |   22 |   22 |    23 |    23 |
|   172 | 2018-10-05 |      81 | 1995-10-12 | 1995-12-31 |   22 |   22 |    22 |    23 |
|   173 | 2018-10-05 |       5 | 1996-10-06 | 1996-10-10 |   21 |   21 |    22 |    22 |
|   174 | 2018-10-05 |      82 | 1996-10-11 | 1996-12-31 |   21 |   21 |    21 |    22 |
|   175 | 2018-10-05 |       5 | 1997-10-06 | 1997-10-10 |   20 |   20 |    21 |    21 |
|   176 | 2018-10-05 |      82 | 1997-10-11 | 1997-12-31 |   20 |   20 |    20 |    21 |
|   177 | 2018-10-05 |       5 | 1998-10-06 | 1998-10-10 |   19 |   19 |    20 |    20 |
|   178 | 2018-10-05 |      82 | 1998-10-11 | 1998-12-31 |   19 |   19 |    19 |    20 |
|   179 | 2018-10-05 |       5 | 1999-10-06 | 1999-10-10 |   18 |   18 |    19 |    19 |
|   180 | 2018-10-05 |      82 | 1999-10-11 | 1999-12-31 |   18 |   18 |    18 |    19 |
|   181 | 2018-10-05 |       4 | 2000-10-06 | 2000-10-09 |   17 |   17 |    18 |    18 |
|   182 | 2018-10-05 |      83 | 2000-10-10 | 2000-12-31 |   17 |   17 |    17 |    18 |
|   183 | 2018-10-05 |       4 | 2001-10-06 | 2001-10-09 |   16 |   16 |    17 |    17 |
|   184 | 2018-10-05 |      83 | 2001-10-10 | 2001-12-31 |   16 |   16 |    16 |    17 |
|   185 | 2018-10-05 |       4 | 2002-10-06 | 2002-10-09 |   15 |   15 |    16 |    16 |
|   186 | 2018-10-05 |      83 | 2002-10-10 | 2002-12-31 |   15 |   15 |    15 |    16 |
|   187 | 2018-10-05 |       4 | 2003-10-06 | 2003-10-09 |   14 |   14 |    15 |    15 |
|   188 | 2018-10-05 |      83 | 2003-10-10 | 2003-12-31 |   14 |   14 |    14 |    15 |
|   189 | 2018-10-05 |       3 | 2004-10-06 | 2004-10-08 |   13 |   13 |    14 |    14 |
|   190 | 2018-10-05 |      84 | 2004-10-09 | 2004-12-31 |   13 |   13 |    13 |    14 |
|   191 | 2018-10-05 |       3 | 2005-10-06 | 2005-10-08 |   12 |   12 |    13 |    13 |
|   192 | 2018-10-05 |      84 | 2005-10-09 | 2005-12-31 |   12 |   12 |    12 |    13 |
|   193 | 2018-10-05 |       3 | 2006-10-06 | 2006-10-08 |   11 |   11 |    12 |    12 |
|   194 | 2018-10-05 |      84 | 2006-10-09 | 2006-12-31 |   11 |   11 |    11 |    12 |
|   195 | 2018-10-05 |       3 | 2007-10-06 | 2007-10-08 |   10 |   10 |    11 |    11 |
|   196 | 2018-10-05 |      84 | 2007-10-09 | 2007-12-31 |   10 |   10 |    10 |    11 |
|   197 | 2018-10-05 |       2 | 2008-10-06 | 2008-10-07 |    9 |    9 |    10 |    10 |
|   198 | 2018-10-05 |      85 | 2008-10-08 | 2008-12-31 |    9 |    9 |     9 |    10 |
|   199 | 2018-10-05 |       2 | 2009-10-06 | 2009-10-07 |    8 |    8 |     9 |     9 |
|   200 | 2018-10-05 |      85 | 2009-10-08 | 2009-12-31 |    8 |    8 |     8 |     9 |
|   201 | 2018-10-05 |       2 | 2010-10-06 | 2010-10-07 |    7 |    7 |     8 |     8 |
|   202 | 2018-10-05 |      85 | 2010-10-08 | 2010-12-31 |    7 |    7 |     7 |     8 |
|   203 | 2018-10-05 |       2 | 2011-10-06 | 2011-10-07 |    6 |    6 |     7 |     7 |
|   204 | 2018-10-05 |      85 | 2011-10-08 | 2011-12-31 |    6 |    6 |     6 |     7 |
|   205 | 2018-10-05 |       1 | 2012-10-06 | 2012-10-06 |    5 |    5 |     6 |     6 |
|   206 | 2018-10-05 |      86 | 2012-10-07 | 2012-12-31 |    5 |    5 |     5 |     6 |
|   207 | 2018-10-05 |       1 | 2013-10-06 | 2013-10-06 |    4 |    4 |     5 |     5 |
|   208 | 2018-10-05 |      86 | 2013-10-07 | 2013-12-31 |    4 |    4 |     4 |     5 |
|   209 | 2018-10-05 |       1 | 2014-10-06 | 2014-10-06 |    3 |    3 |     4 |     4 |
|   210 | 2018-10-05 |      86 | 2014-10-07 | 2014-12-31 |    3 |    3 |     3 |     4 |
|   211 | 2018-10-05 |       1 | 2015-10-06 | 2015-10-06 |    2 |    2 |     3 |     3 |
|   212 | 2018-10-05 |      86 | 2015-10-07 | 2015-12-31 |    2 |    2 |     2 |     3 |
|   213 | 2018-10-05 |      87 | 2016-10-06 | 2016-12-31 |    1 |    1 |     1 |     2 |
|   214 | 2018-10-05 |      87 | 2017-10-06 | 2017-12-31 |    0 |    0 |     0 |     1 |

``````

Demonstrated online here:  http://rextester.com/EVEKT82871

For Oracle, the following can be used:

, EXTRACT(YEAR FROM VisitDate) - EXTRACT(YEAR FROM dob)

- ( CASE

WHEN EXTRACT(MONTH FROM dob) > EXTRACT(MONTH FROM VisitDate)

OR ( EXTRACT(MONTH FROM dob) = EXTRACT(MONTH FROM VisitDate)

AND EXTRACT(DAY FROM dob) > EXTRACT(DAY FROM VisitDate)

)               THEN 1 ELSE 0  END )    AS age

3
2,051 Views
More years at various grindstones than I care to remember. Some interesting grindstones though: Shipbuilding, Aerospace ..... IT

Get vaccinated; Social distance; Wear a mask
CERTIFIED EXPERT
Top Expert 2014

Commented:
1. rather than getdate(), a stored proc should accept both birthdate and AgeOnDate parameters.

2. I've run some tests in the Access environment and the performance of the Case statement can be improved if you convert the month and day of both the birthdate and the AgeOnDate with the Month() and Day() functions, multiplying the Month() result by 100 and adding the Day() result.  You can use the comparison as part of your year difference calculation.  I even ditched the DateDiff() function for a subtraction of the two Year() functions.

IT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
@PortletPaul,
Can you explain what each field means? Because for your example I would say that Unreliable ages are both correct but reliable age it isn't.
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
Oracle syntax
``````select
CustomerID
, DOB
, VisitDate
, EXTRACT(YEAR FROM VisitDate) - EXTRACT(YEAR FROM dob)
- ( CASE
WHEN EXTRACT(MONTH FROM dob) > EXTRACT(MONTH FROM VisitDate)
OR ( EXTRACT(MONTH FROM dob) = EXTRACT(MONTH FROM VisitDate)
AND EXTRACT(DAY FROM dob) > EXTRACT(DAY FROM VisitDate)
)
THEN 1
ELSE 0
END )    AS age_at_visit
from sample
``````
Get vaccinated; Social distance; Wear a mask
CERTIFIED EXPERT
Top Expert 2014

Commented:
@PortletPaul

This question is in the SQL Server zone.  You just posted "Oracle syntax".  Is this an Oracle question?