<

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

Published on
6,360 Points
1,560 Views
3 Endorsements
Last Modified:
PortletPaul
More years at various grindstones than I care to remember. Some interesting grindstones though: Shipbuilding, Aerospace ..... IT
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
         WHEN DATEADD(YEAR,DATEDIFF(YEAR,BirthDate, GetDate()),BirthDate)> GetDate()
         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
                        SELECT DATEADD(DAY, 1, BirthDate)
                        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
                             WHEN DATEADD(YEAR,DATEDIFF(YEAR,BirthDate, GetDate()),BirthDate)> GetDate() 
                           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
Author:PortletPaul
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free