How to calculate age in T-SQL with years months and days

Question

What would be the best way to calculate someone s age in years  months  and days in T-SQL  SQL Server 2000    The datediff function doesn t handle year boundaries well  plus getting the months and days separate will be a bear   I know I can do it on the client side relatively easily  but I d like to have it done in my stored procedure

User · Answer

Here is how I calculate the age given a birth date and the current date   select case              when cast getdate   as date    cast dateadd year   datediff year   1996-09-09   getdate       1996-09-09   as date                  then dateDiff yyyy  1996-09-09  dateadd year  0  getdate                 else dateDiff yyyy  1996-09-09  dateadd year  -1  getdate             end as MemberAge go

User · Answer

DateTime values in T-SQL are stored as floats  You can just subtract the dates from each other and you now have a new date that is the timespan between them   declare  birthdate datetime set  birthdate    6 15 1974   --age in years - short version print year getdate   -  birthdate  - year 0   --age in years - visualization declare  mindate datetime declare  span datetime  set  mindate   0 set  span   getdate   -  birthdate  print  mindate print  birthdate print getdate   print  span --substract minyear from spanyear to get age in years print year  span  - year  mindate  print month  span  print day  span

User · Answer

Try this     SELECT CASE WHEN   DATEADD year DATEDIFF year   datestart    dateend     datestart   gt   dateend  THEN DATEDIFF year   datestart    dateend  -1 ELSE DATEDIFF year   datestart    dateend  END   Basically the  DateDiff  year      gives you the age the person will turn this year  so i have just add a case statement to say  if they have not had a birthday yet this year  then subtract 1 year  else return the value

User · Answer

There is an easy way  based on the hours between the two days BUT with the end date truncated    SELECT CAST DATEDIFF hour Birthdate CAST GETDATE   as Date   8766 0 as INT  AS Age FROM  lt YourTable gt    This one has proven to be extremely accurate and reliable  If it weren t for the inner CAST on the GETDATE   it might flip the birthday a few hours before midnight but  with the CAST  it is dead on with the age changing over at exactly midnight

User · Answer

declare  StartDate datetime    2016-01-31  declare  EndDate datetime    2016-02-01  SELECT  StartDate AS  StartDate          EndDate AS  EndDate         DATEDIFF Year  StartDate  EndDate  - CASE WHEN DATEADD Year DATEDIFF Year  StartDate  EndDate    StartDate   gt   EndDate THEN 1 ELSE 0 END AS  Years         DATEDIFF Month  DATEADD Year DATEDIFF Year  StartDate  EndDate  - CASE WHEN DATEADD Year DATEDIFF Year  StartDate  EndDate    StartDate   gt   EndDate THEN 1 ELSE 0 END  StartDate    EndDate  - CASE WHEN DATEADD Month  DATEDIFF Month DATEADD Year DATEDIFF Year  StartDate  EndDate  - CASE WHEN DATEADD Year DATEDIFF Year  StartDate  EndDate    StartDate   gt   EndDate THEN 1 ELSE 0 END  StartDate   EndDate     StartDate   gt   EndDate THEN 1 ELSE 0 END AS  Months         DATEDIFF Day  DATEADD Month DATEDIFF Month   DATEADD Year DATEDIFF Year  StartDate  EndDate  - CASE WHEN DATEADD Year DATEDIFF Year  StartDate  EndDate    StartDate   gt   EndDate THEN 1 ELSE 0 END  StartDate    EndDate  - CASE WHEN DATEADD Month  DATEDIFF Month DATEADD Year DATEDIFF Year  StartDate  EndDate  - CASE WHEN DATEADD Year DATEDIFF Year  StartDate  EndDate    StartDate   gt   EndDate THEN 1 ELSE 0 END  StartDate   EndDate     StartDate   gt   EndDate THEN 1 ELSE 0 END   DATEADD Year DATEDIFF Year  StartDate  EndDate  - CASE WHEN DATEADD Year DATEDIFF Year  StartDate  EndDate    StartDate   gt   EndDate THEN 1 ELSE 0 END  StartDate     EndDate  - CASE WHEN DATEADD Day DATEDIFF Day  DATEADD Month DATEDIFF Month   DATEADD Year DATEDIFF Year  StartDate  EndDate  - CASE WHEN DATEADD Year DATEDIFF Year  StartDate  EndDate    StartDate   gt   EndDate THEN 1 ELSE 0 END  StartDate    EndDate  - CASE WHEN DATEADD Month  DATEDIFF Month DATEADD Year DATEDIFF Year  StartDate  EndDate  - CASE WHEN DATEADD Year DATEDIFF Year  StartDate  EndDate    StartDate   gt   EndDate THEN 1 ELSE 0 END  StartDate   EndDate     StartDate   gt   EndDate THEN 1 ELSE 0 END   DATEADD Year DATEDIFF Year  StartDate  EndDate  - CASE WHEN DATEADD Year DATEDIFF Year  StartDate  EndDate    StartDate   gt   EndDate THEN 1 ELSE 0 END  StartDate     EndDate  DATEADD Month DATEDIFF Month   DATEADD Year DATEDIFF Year  StartDate  EndDate  - CASE WHEN DATEADD Year DATEDIFF Year  StartDate  EndDate    StartDate   gt   EndDate THEN 1 ELSE 0 END  StartDate    EndDate  - CASE WHEN DATEADD Month  DATEDIFF Month DATEADD Year DATEDIFF Year  StartDate  EndDate  - CASE WHEN DATEADD Year DATEDIFF Year  StartDate  EndDate    StartDate   gt   EndDate THEN 1 ELSE 0 END  StartDate   EndDate     StartDate   gt   EndDate THEN 1 ELSE 0 END   DATEADD Year DATEDIFF Year  StartDate  EndDate  - CASE WHEN DATEADD Year DATEDIFF Year  StartDate  EndDate    StartDate   gt   EndDate THEN 1 ELSE 0 END  StartDate     gt   EndDate THEN 1 ELSE 0 END AS  Days

User · Answer

CREATE FUNCTION DBO GET AGE    DATE AS DATETIME   RETURNS VARCHAR MAX  AS BEGIN  DECLARE  YEAR  AS VARCHAR 50       DECLARE  MONTH AS VARCHAR 50       DECLARE  DAYS  AS VARCHAR 50       DECLARE  RESULT AS VARCHAR MAX        SET  YEAR    CONVERT VARCHAR  SELECT DATEDIFF MONTH CASE WHEN DAY  DATE   gt  DAY GETDATE    THEN DATEADD MONTH 1  DATE  ELSE  DATE END GETDATE      12    SET  MONTH   CONVERT VARCHAR  SELECT DATEDIFF MONTH CASE WHEN DAY  DATE   gt  DAY GETDATE    THEN DATEADD MONTH 1  DATE  ELSE  DATE END GETDATE      12    SET  DAYS   DATEDIFF DD DATEADD MM CONVERT INT CONVERT INT  YEAR  12   CONVERT INT  MONTH    DATE  GETDATE     SET  RESULT    RIGHT  00     YEAR  2      YEARS     RIGHT  00     MONTH  2      MONTHS     RIGHT  00     DAYS  2      DAYS    RETURN  RESULT END  SELECT DBO GET AGE  04 12 1986

User · Answer

Here is some T-SQL that gives you the number of years  months  and days since the day specified in  date   It takes into account the fact that DATEDIFF   computes the difference without considering what month or day it is  so the month diff between 8 31 and 9 1 is 1 month  and handles that with a case statement that decrements the result where appropriate   DECLARE  date datetime   tmpdate datetime   years int   months int   days int SELECT  date    2 29 04   SELECT  tmpdate    date  SELECT  years   DATEDIFF yy   tmpdate  GETDATE    - CASE WHEN  MONTH  date   gt  MONTH GETDATE     OR  MONTH  date    MONTH GETDATE    AND DAY  date   gt  DAY GETDATE     THEN 1 ELSE 0 END SELECT  tmpdate   DATEADD yy   years   tmpdate  SELECT  months   DATEDIFF m   tmpdate  GETDATE    - CASE WHEN DAY  date   gt  DAY GETDATE    THEN 1 ELSE 0 END SELECT  tmpdate   DATEADD m   months   tmpdate  SELECT  days   DATEDIFF d   tmpdate  GETDATE     SELECT  years   months   days

User · Answer

select DOB as Birthdate         YEAR GETDATE    as ThisYear          YEAR getdate    - EAR date1  as Age    from TableName

User · Answer

Quite Old question  but I want to share what I have done to calculate age      Declare  BirthDate As DateTime Set  BirthDate    1994-11-02   SELECT DATEDIFF YEAR  BirthDate GETDATE    -  CASE  WHEN MONTH  BirthDate  gt  MONTH GETDATE    THEN 1  WHEN MONTH  BirthDate   MONTH GETDATE    AND DAY  BirthDate   gt  DAY GETDATE    THEN 1  Else 0 END

User · Answer

Here is SQL code that gives you the number of years  months  and days since the sysdate   Enter value for input birth date this format dd mon yy   note  input same value birth date  for years  months  amp  days such as 01-mar-85  select trunc  sysdate -to date   amp input birth date dd mon yy    365  years  trunc mod   sysdate -to date   amp input birth date dd mon yy    365 1  12  months  trunc  mod  mod  sysdate -to date   amp input birth date dd mon yy    365 1  12  1  30  1  days   from dual

User · Answer

Are you trying to calculate the total days months years of an age  do you have a starting date  Or are you trying to dissect it  ex  24 years  1 month  29 days    If you have a start date that you re working with  datediff will output the total days months years with the following commands   Select DateDiff d  1984-07-12   2008-09-11    Select DateDiff m  1984-07-12   2008-09-11    Select DateDiff yyyy  1984-07-12   2008-09-11     with the respective outputs being  8827 290 24    Now  if you wanted to do the dissection method  you d have to subtract the number of years in days  days - 365 years   and then do further math on that to get the months  etc

User · Answer

Here is a  slightly  simpler version   CREATE PROCEDURE dbo CalculateAge       dayOfBirth datetime AS  DECLARE  today datetime   thisYearBirthDay datetime DECLARE  years int   months int   days int  SELECT  today   GETDATE    SELECT  thisYearBirthDay   DATEADD year  DATEDIFF year   dayOfBirth   today    dayOfBirth   SELECT  years   DATEDIFF year   dayOfBirth   today  -  CASE WHEN  thisYearBirthDay  gt   today THEN 1 ELSE 0 END   SELECT  months   MONTH  today -  thisYearBirthDay  - 1  SELECT  days   DAY  today -  thisYearBirthDay  - 1  SELECT  years   months   days GO

User · Answer

DECLARE  DoB AS DATE    1968-10-24  DECLARE  cDate AS DATE   CAST  2000-10-23  AS DATE   SELECT  --Get Year difference DATEDIFF YEAR  DoB  cDate  - --Cases where year difference will be augmented CASE      --If Date of Birth greater than date passed return 0     WHEN YEAR  DoB  - YEAR  cDate   gt   0 THEN DATEDIFF YEAR  DoB  cDate       --If date of birth month less than date passed subtract one year     WHEN MONTH  DoB  - MONTH  cDate   gt  0 THEN 1       --If date of birth day less than date passed subtract one year     WHEN MONTH  DoB  - MONTH  cDate    0 AND DAY  DoB  - DAY  cDate   gt  0 THEN 1       --All cases passed subtract zero     ELSE 0 END

User · Answer

I ve seen the question several times with results outputting Years  Month  Days but never a numeric   decimal result   At least not one that doesn t round incorrectly   I welcome feedback on this function   Might not still need a little adjusting   -- Input to the function is two dates  -- Output is the numeric number of years between the two dates in Decimal 7 4  format  -- Output is always always a possitive number    -- NOTE Output does not handle if difference is greater than 999 9999  -- Logic is based on three steps  -- 1  Is the difference less than 1 year  0 5000  0 3333  0 6667  ect   -- 2  Is the difference exactly a whole number of years  1 2 3  ect    -- 3   Else    The difference is years and some number of days   1 5000  2 3333  7 6667  ect        CREATE Function  dbo   F Get Actual Age   pi date1 datetime  pi date2 datetime  RETURNS Numeric 7 4  AS BEGIN  Declare    l tmp date    DATETIME   l days1       DECIMAL 9 6    l days2       DECIMAL 9 6    l result      DECIMAL 10 6    l years       DECIMAL 7 4      --Check to make sure there is a date for both inputs   IF  pi date1 IS NOT NULL and  pi date2 IS NOT NULL     BEGIN      IF  pi date1  gt   pi date2 --Make sure the  older  date is in  pi date1       BEGIN         SET  l tmp date    pi date2         SET  pi date2    Pi date1         SET  pi date1    l tmp date       END      --Check  1 If date1   1 year is greater than date2  difference must be less than 1 year     IF DATEADD YYYY 1  pi date1   gt   pi date2         BEGIN           --How many days between the two dates  numerator          SET  l days1   DATEDIFF dd  pi date1   pi date2             --subtract 1 year from date2 and calculate days bewteen it and date2           --This is to get the denominator and accounts for leap year  365 or 366 days          SET  l days2   DATEDIFF dd dateadd yyyy -1  pi date2   pi date2           SET  l years    l days1    l days2 -- Do the math       END     ELSE       --Check  2  Are the dates an exact number of years apart        --Calculate years bewteen date1 and date2  then add the years to date1  compare dates to see if exactly the same        IF DATEADD YYYY DATEDIFF YYYY  pi date1  pi date2   pi date1     pi date2           SET  l years   DATEDIFF YYYY  pi date1   pi date2  --AS Years   Exactly even Years  AS Msg       ELSE       BEGIN         --Check  3 The rest of the cases          --Check if datediff  returning years  over or under states the years difference         SET  l years   DATEDIFF YYYY  pi date1   pi date2          IF DATEADD YYYY  l years  pi date1   gt   pi date2           SET  l years    l years -1           --use basicly same logic as in check  1           SET  l days1   DATEDIFF dd DATEADD YYYY  l years  pi date1    pi date2           SET  l days2   DATEDIFF dd dateadd yyyy -1  pi date2   pi date2           SET  l years    l years    l days1    l days2         --SELECT  l years AS Years   Years Plus  AS Msg       END   END   ELSE     SET  l years   0  --If either date was null  RETURN  l Years  --Return the result as decimal 7 4  END

User · Answer

declare  BirthDate datetime declare  TotalYear int declare  TotalMonths int declare  TotalDays int declare  TotalWeeks int declare  TotalHours int declare  TotalMinute int declare  TotalSecond int declare  CurrentDtTime datetime set  BirthDate  1998 01 05 05 04 00   -- Set Your date here set  TotalYear  FLOOR DATEDIFF DAY   BirthDate  GETDATE      365 25  set  TotalMonths  FLOOR DATEDIFF DAY DATEADD year   TotalYear  BirthDate  GetDate      30 436875E  set  TotalDays  FLOOR DATEDIFF DAY  DATEADD month   TotalMonths DATEADD year        TotalYear  BirthDate    GETDATE     set  CurrentDtTime CONVERT datetime CONVERT varchar 50   DATEPART year       GetDate          CONVERT varchar 50   DATEPART MONTH  GetDate               CONVERT varchar 50  DATEPART DAY  GetDate               CONVERT varchar 50  DATEPART HOUR   BirthDate              CONVERT varchar 50  DATEPART MINUTE   BirthDate            CONVERT varchar 50  DATEPART Second   BirthDate    set  TotalHours   DATEDIFF hour   CurrentDtTime  GETDATE    if  TotalHours  lt  0  begin    set  TotalHours   DATEDIFF hour DATEADD Day -1   CurrentDtTime   GETDATE       set  TotalDays   TotalDays -1    end set  TotalMinute  DATEPART MINUTE  GETDATE   -DATEPART MINUTE   BirthDate   if  TotalMinute  lt  0  set  TotalMinute   DATEPART MINUTE  DATEADD hour -1 GETDATE      60-DATEPART MINUTE       BirthDate    set  TotalSecond  DATEPART Second  GETDATE   -DATEPART Second   BirthDate    Print  Your age are   CHAR 13     CONVERT varchar 50    TotalYear    Years         CONVERT varchar 50   TotalMonths     Months         CONVERT varchar 50   TotalDays    Days         CONVERT varchar 50   TotalHours    Hours         CONVERT varchar 50   TotalMinute    Minutes          CONVERT varchar 50   TotalSecond    Seconds     char 13         Your are born at day of week was -     CONVERT varchar 50  DATENAME dw          BirthDate       char 13  char 13     Your Birthdate to till date your    CHAR 13    Years -     CONVERT varchar 50   FLOOR DATEDIFF DAY   BirthDate  GETDATE          365 25        Months -     CONVERT varchar 50  DATEDIFF MM  BirthDate getdate           Weeks -     CONVERT varchar 50  DATEDIFF wk  BirthDate getdate          Days -     CONVERT varchar 50  DATEDIFF dd  BirthDate getdate     char 13     Hours -     CONVERT varchar 50  DATEDIFF HH  BirthDate getdate          Minutes -     CONVERT varchar 50  DATEDIFF mi  BirthDate getdate          Seconds -     CONVERT varchar 50  DATEDIFF ss  BirthDate getdate       Output  Your age are 22 Years  0 Months  2 Days  11 Hours  30 Minutes  16 Seconds   Your are born at day of week was - Monday  Your Birthdate to till date your  Years - 22   Months - 264   Weeks - 1148   Days - 8037 Hours - 192899   Minutes - 11573970   Seconds - 694438216

User · Answer

I use this Function I modified  the Days part  From  Dane answer  https   stackoverflow com a 57720 2097023  CREATE FUNCTION dbo EdadAMD                FECHA DATETIME           RETURNS NVARCHAR 10      AS     BEGIN         DECLARE              tmpdate DATETIME              years   INT              months  INT              days    INT              EdadAMD NVARCHAR 10            SELECT  tmpdate    FECHA           SELECT  years   DATEDIFF yy   tmpdate  GETDATE    - CASE                                           WHEN  MONTH  FECHA   gt     MONTH GETDATE                                                  OR                                                   MONTH  FECHA    MONTH GETDATE                                              AND DAY  FECHA   gt  DAY GETDATE                                                THEN                                                 1                                             ELSE                                                 0                                     END      SELECT  tmpdate   DATEADD yy   years   tmpdate       SELECT  months   DATEDIFF m   tmpdate  GETDATE    - CASE                               WHEN DAY  FECHA   gt  DAY GETDATE    THEN                                                             1                                                         ELSE                                                             0                                                     END      SELECT  tmpdate   DATEADD m   months   tmpdate        IF MONTH  FECHA    MONTH GETDATE           AND DAY  FECHA   gt  DAY GETDATE              SELECT  days                DAY EOMONTH GETDATE    -1   -  DAY  FECHA  - DAY GETDATE          ELSE         SELECT  days   DATEDIFF d   tmpdate  GETDATE          SELECT  EdadAMD   CONCAT  years   a    months   m    days   d         RETURN  EdadAMD   END   GO   It works pretty well

User · Answer

For the ones that want to create a calculated column in a table to store the age   CASE WHEN DateOfBirth lt  DATEADD YEAR   DATEPART YEAR  GETDATE    - DATEPART YEAR  DateOfBirth   -1  GETDATE          THEN DATEPART YEAR  GETDATE    - DATEPART YEAR  DateOfBirth       ELSE DATEPART YEAR  GETDATE    - DATEPART YEAR  DateOfBirth  -1 END

User · Answer

DECLARE  BirthDate datetime   AgeInMonths int SET  BirthDate    10 5 1971  SET  AgeInMonths                              -- Determine the age in  months old         DATEDIFF MONTH   BirthDate  GETDATE     --  Get the difference in months     - CASE WHEN DATEPART DAY GETDATE          --  If today was the 1st to 4th                 lt  DATEPART DAY  BirthDate       --    or before the birth day of month             THEN 1 ELSE 0 END                  --       don t count the month  SELECT  AgeInMonths   12 as AgeYrs            -- Divide by 12 months to get the age in years         AgeInMonths   12 as AgeXtraMonths     -- Get the remainder of dividing by 12 months   extra months        DATEDIFF DAY                           -- For the extra days  find the difference between                   DATEADD MONTH   AgeInMonths   -- 1  Last Monthly Birthday                                  BirthDate     --      if birthdays were celebrated monthly                  GETDATE    as AgeXtraDays     -- 2  Today s date

User · Answer

There is another method for calculate age is  See below table      FirstName       LastName    DOB     sai             krishnan    1991-11-04     Harish          S A         1998-10-11   For finding age you can calculate through month    Select datediff MONTH DOB getdate    12 as dates from  Organization   Employee    Result will be   firstname   dates sai         27 Harish      20

User · Answer

create  procedure getDatedifference         startdate datetime       enddate datetime   as begin     declare  monthToShow int     declare  dayToShow int      --set  startdate  01 21 1934      --set  enddate getdate        if  DAY  startdate   gt  DAY  enddate           begin             set  dayToShow 0              if  month  startdate   gt  month  enddate                   begin                     set  monthToShow    12-month  startdate   month  enddate -1                  end             else if  month  startdate   lt  month  enddate                   begin                     set  monthToShow     month  enddate -month  startdate  -1                  end             else                begin                    set  monthToShow   11                end             -- set  monthToShow  convert int  DATEDIFF mm 0 DATEADD dd DATEDIFF dd 0  enddate - DATEDIFF dd 0  startdate  0   -  convert int FLOOR DATEDIFF day   startdate   enddate    365 25   12  -1                          if  monthToShow lt 0                           begin                             set  monthToShow 0                          end                        declare  amonthbefore integer                       set  amonthbefore Month  enddate -1                           if  amonthbefore 0                               begin                                 set  amonthbefore 12                               end                         if   amonthbefore  in 1 3 5 7 8 10 12                             begin                             set  dayToShow 31-DAY  startdate  DAY  enddate                            end                       if   amonthbefore 2                           begin                            IF  YEAR   enddate     4   0 AND YEAR   enddate     100    0  OR  YEAR   enddate     400   0                                  begin                                     set  dayToShow 29-DAY  startdate  DAY  enddate                                    end                            else                                begin                                    set  dayToShow 28-DAY  startdate  DAY  enddate                             end                       end                       if   amonthbefore in  4 6 9 11                           begin                            set  dayToShow 30-DAY  startdate  DAY  enddate                          end                  end     else         begin           --set  monthToShow convert int  DATEDIFF mm 0 DATEADD dd DATEDIFF dd 0  enddate - DATEDIFF dd 0  startdate  0   -  convert int FLOOR DATEDIFF day   startdate   enddate    365 25   12             if  month  enddate  lt  month  startdate                 begin                  set  monthToShow 12  month  enddate -month  startdate                 end           else               begin                 set  monthToShow   month  enddate -month  startdate                 end           set  dayToShow DAY  enddate -DAY  startdate          end      SELECT         FLOOR DATEDIFF day   startdate   enddate    365 25  as  yearToShow              monthToShow as  monthToShow   dayToShow as dayToShow           convert varchar FLOOR DATEDIFF day   startdate   enddate    365 25      Year     convert varchar  monthToShow     months   convert varchar  dayToShow    days   as age      return end

User · Answer

Simple way to get age as text is as below   Select cast  DATEDIFF m  date of birth  GETDATE    12  as varchar      Y  amp              cast  DATEDIFF m  date of birth  GETDATE    12  as varchar      M  as Age   Results Format will be     63 Y  amp  2 M

User · Answer

Implemented by arithmetic with ISO formatted date   declare  now date  dob date   now i int  dob i int   days in birth month int declare  years int   months int   days int set  now    2013-02-28   set  dob    2012-02-29  -- Date of Birth  set  now i   convert varchar 8   now 112  -- iso formatted  20130228 set  dob i   convert varchar 8   dob 112  -- iso formatted  20120229 set  years      now i -  dob i  10000 --  20130228 - 20120229  10000   0 years  set  months   1200    month  now - month  dob   100   day  now  - day  dob   100  12 --  1200   0228 - 0229  100   12   11 months  set  days in birth month   day dateadd d -1 left convert varchar 8  dateadd m 1  dob  112  6   01    set  days    sign day  now  - day  dob   1  2    day  now  - day  dob                sign day  dob  - day  now   1  2     days in birth month - day  dob    day  now   --    -1 1  2  28 - 29     1 1  2  29 - 29   28   -- Explain  if the days of now is bigger than the days of birth  then diff the two days --          else add the days of now and the distance from the date of birth to the end of the birth month  select  years  months  days -- 0  11  28    Test Cases  The approach of days is different from the accepted answer  the differences shown in the comments below          dob        now  years  months  days  2012-02-29 2013-02-28      0      11    28  --Days will be 30 if calculated by the approach in accepted answer   2012-02-29 2016-02-28      3      11    28  --Days will be 31 if calculated by the approach in accepted answer  since the day of birth will be changed to 28 from 29 after dateadd by years   2012-02-29 2016-03-31      4       1     2 2012-01-30 2016-02-29      4       0    30 2012-01-30 2016-03-01      4       1     2  --Days will be 1 if calculated by the approach in accepted answer  since the day of birth will be changed to 30 from 29 after dateadd by years  2011-12-30 2016-02-29      4       1    30   An short version of Days by case statement   set  days   CASE WHEN day  now   gt   day  dob  THEN day  now  - day  dob                   ELSE  days in birth month - day  dob    day  now  END   If you want the age of years and months only  it could be simpler  set  years      now i 100 -  dob i 100  100 set  months   12   month  now  - month  dob   12  select  years  months -- 1  0   NOTE  A very useful link of SQL Server Date Formats

User · Answer

The same sort of thing as a function   create function  dbo   Age   dayOfBirth datetime   today datetime     RETURNS varchar 100  AS  Begin DECLARE  thisYearBirthDay datetime DECLARE  years int   months int   days int  set  thisYearBirthDay   DATEADD year  DATEDIFF year   dayOfBirth   today    dayOfBirth  set  years   DATEDIFF year   dayOfBirth   today  -  CASE WHEN  thisYearBirthDay  gt   today THEN 1 ELSE 0 END  set  months   MONTH  today -  thisYearBirthDay  - 1 set  days   DAY  today -  thisYearBirthDay  - 1  return cast  years as varchar 2       years     cast  months as varchar 2       months     cast  days as varchar 3       days  end

User · Answer

SELECT DOB AS Birthdate          YEAR GETDATE    AS ThisYear         YEAR getdate    - YEAR DOB  AS Age FROM tableprincejain

[tsql] How to calculate age in T-SQL with years, months, and days

Examples related to tsql

Examples related to datediff