How to calculate somebody's age with MSSQL

  • First way of calculating somebody's age would be to do a datediff with year: datediff (year, birthdate, current_timestamp). But that does not really work because of because MSSQL counts the years and does not care about the days. So if you have a birth date after today but before then end of the year, you will be off by a year.
  • The second way of calculating is to extract the days, then extrapolate the year from that. The only problem with that are the leap years. So instead of: datediff (year, birthdate, current_timestamp) you can try datediff(day,birthdate,current_timestamp) / 365.25. This will work properly for most people, as long as they are not very old. As you go before 1900, you will be missing a day. To make it work a little better, you can use: floor(datediff(day,birthdate,current_timestamp) / 365.25). This will drop the decimal portion of the year.
  • The better way, in my opinion, is to see if the date if greater than their birthday or not, then calculate the year appropriately. This what we will use now.

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008

Data used

  1. USE sql911;
  2. go
  3. SELECT empno,firstnme,lastname,birthdate
  4. FROM emps;
  5. go
empno  firstnme     lastname        birthdate
------ ------------ --------------- -----------------------
000010 CHRISTINE    HAAS            1950-03-24 00:00:00.000
000020 MICHAEL      THOMPSON        1964-09-02 00:00:00.000
000030 SALLY!       KWAN            1957-12-11 00:00:00.000
000050 JOHN         GEYER           1942-04-15 00:00:00.000
000060 IRVING@      STERN           1962-02-07 00:00:00.000
000070 EVA          PULASKI         1969-12-26 00:00:00.000
000090 EILEEN#      HENDERSON       1957-12-15 00:00:00.000
000100 THEODORE     SPENSER         1973-07-18 00:00:00.000
000110 VINCENZO.    LUCCHESSI       1946-06-05 00:00:00.000
000120 SEAN         O CONNELL       1959-05-18 00:00:00.000
000130 DOLORES,     QUINTANA        1942-04-15 00:00:00.000
000140 HEATHER      NICHOLLS        1962-08-19 00:00:00.000
000150 BRUCE        ADAMSON         1963-12-17 00:00:00.000
000160 ELIZABETH    PIANKA          1971-11-12 00:00:00.000
000170 MASATOSHI%   YOSHIMURA       1967-08-05 00:00:00.000
000180 MARILYN      SCOUTTEN        1965-09-21 00:00:00.000
000190 JAMES&       WALKER          1969-01-25 00:00:00.000
000200 DAVID        BROWN           1957-12-29 00:00:00.000
000210 WILLIAM*     JONES           1969-09-23 00:00:00.000
000220 JENNIFER]    LUTZ            1964-10-19 00:00:00.000
000230 JAMES        JEFFERSON       1951-12-30 00:00:00.000
000240 SALVATORE[   MARINO          1970-10-31 00:00:00.000
000250 DANIEL       SMITH           1956-06-12 00:00:00.000
000260 SYBIL\       JOHNSON         1953-05-05 00:00:00.000
000270 MARIA        PEREZ           1969-12-26 00:00:00.000
000280 ETHEL/       SCHNEIDER       1952-10-28 00:00:00.000
000290 JOHN         PARKER          1963-02-09 00:00:00.000
000300 PHILIP       SMITH           1953-05-27 00:00:00.000
000310 MAUDE        SETRIGHT        1947-11-21 00:00:00.000
000320 RAMLAL       MEHTA           1949-03-11 00:00:00.000
000330 WING         LEE             1958-02-18 00:00:00.000
000340 JASON        GOUNOT          1942-12-17 00:00:00.000

(32 row(s) affected)

Calculating age

  1. SELECT empno,firstnme,lastname,birthdate,
  2.        CASE WHEN DATEADD(YEAR, DATEDIFF (YEAR, birthdate, CURRENT_TIMESTAMP), birthdate) > CURRENT_TIMESTAMP
  3.             THEN DATEDIFF(YEAR, birthdate, CURRENT_TIMESTAMP) - 1
  4.             ELSE DATEDIFF(YEAR, birthdate, CURRENT_TIMESTAMP)
  5.        END AS 'Employee age'
  6. FROM emps;
  7. go
empno  firstnme     lastname        birthdate               Employee age
------ ------------ --------------- ----------------------- ------------
000010 CHRISTINE    HAAS            1950-03-24 00:00:00.000 57
000020 MICHAEL      THOMPSON        1964-09-02 00:00:00.000 43
000030 SALLY!       KWAN            1957-12-11 00:00:00.000 50
000050 JOHN         GEYER           1942-04-15 00:00:00.000 65
000060 IRVING@      STERN           1962-02-07 00:00:00.000 45
000070 EVA          PULASKI         1969-12-26 00:00:00.000 38
000090 EILEEN#      HENDERSON       1957-12-15 00:00:00.000 50
000100 THEODORE     SPENSER         1973-07-18 00:00:00.000 34
000110 VINCENZO.    LUCCHESSI       1946-06-05 00:00:00.000 61
000120 SEAN         O CONNELL       1959-05-18 00:00:00.000 48
000130 DOLORES,     QUINTANA        1942-04-15 00:00:00.000 65
000140 HEATHER      NICHOLLS        1962-08-19 00:00:00.000 45
000150 BRUCE        ADAMSON         1963-12-17 00:00:00.000 44
000160 ELIZABETH    PIANKA          1971-11-12 00:00:00.000 36
000170 MASATOSHI%   YOSHIMURA       1967-08-05 00:00:00.000 40
000180 MARILYN      SCOUTTEN        1965-09-21 00:00:00.000 42
000190 JAMES&       WALKER          1969-01-25 00:00:00.000 38
000200 DAVID        BROWN           1957-12-29 00:00:00.000 50
000210 WILLIAM*     JONES           1969-09-23 00:00:00.000 38
000220 JENNIFER]    LUTZ            1964-10-19 00:00:00.000 43
000230 JAMES        JEFFERSON       1951-12-30 00:00:00.000 56
000240 SALVATORE[   MARINO          1970-10-31 00:00:00.000 37
000250 DANIEL       SMITH           1956-06-12 00:00:00.000 51
000260 SYBIL\       JOHNSON         1953-05-05 00:00:00.000 54
000270 MARIA        PEREZ           1969-12-26 00:00:00.000 38
000280 ETHEL/       SCHNEIDER       1952-10-28 00:00:00.000 55
000290 JOHN         PARKER          1963-02-09 00:00:00.000 44
000300 PHILIP       SMITH           1953-05-27 00:00:00.000 54
000310 MAUDE        SETRIGHT        1947-11-21 00:00:00.000 60
000320 RAMLAL       MEHTA           1949-03-11 00:00:00.000 58
000330 WING         LEE             1958-02-18 00:00:00.000 49
000340 JASON        GOUNOT          1942-12-17 00:00:00.000 65

(32 row(s) affected)
  • You should use current_timestamp instead of getdate() since:
    1. current_timestamp is part of the SQL standard and getdate() is not.
    2. Microsoft has announced that they will deprecate getdate().