<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

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

Published on
6,259 Points
1,459 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
Comment
Author:PortletPaul
  • 2
4 Comments
LVL 48

Expert Comment

by:aikimark
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.

3. It would be helpful to your reader if you explained how the DateDiff() function works in general.  This will help your reader understand the function's use and applicability in context.
0
LVL 55

Expert Comment

by:Vitor Montalvão
@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.
0
LVL 51

Author Comment

by:PortletPaul
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

Open in new window

1
LVL 48

Expert Comment

by:aikimark
@PortletPaul

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

Featured Post

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Join & Write a Comment

Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month