<p>What would be the best way to calculate someone's age in years, months, and days in T-SQL (SQL Server 2000)?</p>

<p>The <code>datediff</code> 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 <a href="http://en.wikipedia.org/wiki/Stored_procedure" rel="noreferrer">stored procedure</a>.</p>

[{'answer_id': 57642, 'author': 'Michael Runyon', 'author_id': 5405, 'author_profile': 'https://Stackoverflow.com/users/5405', 'pm_score': 0, 'selected': False, 'text': "<p>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)?</p>\n\n<p>If you have a start date that you're working with, datediff will output the total days/months/years with the following commands:</p>\n\n<pre><code>Select DateDiff(d,'1984-07-12','2008-09-11')\n\nSelect DateDiff(m,'1984-07-12','2008-09-11')\n\nSelect DateDiff(yyyy,'1984-07-12','2008-09-11')\n</code></pre>\n\n<p>with the respective outputs being (8827/290/24).</p>\n\n<p>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.</p>\n"}, {'answer_id': 57720, 'author': 'Dane', 'author_id': 2929, 'author_profile': 'https://Stackoverflow.com/users/2929', 'pm_score': 7, 'selected': True, 'text': "<p>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.</p>\n\n<pre><code>DECLARE @date datetime, @tmpdate datetime, @years int, @months int, @days int\nSELECT @date = '2/29/04'\n\nSELECT @tmpdate = @date\n\nSELECT @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\nSELECT @tmpdate = DATEADD(yy, @years, @tmpdate)\nSELECT @months = DATEDIFF(m, @tmpdate, GETDATE()) - CASE WHEN DAY(@date) &gt; DAY(GETDATE()) THEN 1 ELSE 0 END\nSELECT @tmpdate = DATEADD(m, @months, @tmpdate)\nSELECT @days = DATEDIFF(d, @tmpdate, GETDATE())\n\nSELECT @years, @months, @days\n</code></pre>\n"}, {'answer_id': 57748, 'author': 'Leonardo', 'author_id': 1014, 'author_profile': 'https://Stackoverflow.com/users/1014', 'pm_score': 2, 'selected': False, 'text': '<p>Here is a (slightly) simpler version:</p>\n\n<pre><code>CREATE PROCEDURE dbo.CalculateAge \n    @dayOfBirth datetime\nAS\n\nDECLARE @today datetime, @thisYearBirthDay datetime\nDECLARE @years int, @months int, @days int\n\nSELECT @today = GETDATE()\n\nSELECT @thisYearBirthDay = DATEADD(year, DATEDIFF(year, @dayOfBirth, @today), @dayOfBirth)\n\nSELECT @years = DATEDIFF(year, @dayOfBirth, @today) - (CASE WHEN @thisYearBirthDay &gt; @today THEN 1 ELSE 0 END)\n\nSELECT @months = MONTH(@today - @thisYearBirthDay) - 1\n\nSELECT @days = DAY(@today - @thisYearBirthDay) - 1\n\nSELECT @years, @months, @days\nGO\n</code></pre>\n'}, {'answer_id': 2809382, 'author': 'simon831', 'author_id': 107062, 'author_profile': 'https://Stackoverflow.com/users/107062', 'pm_score': 2, 'selected': False, 'text': "<p>The same sort of thing as a function.</p>\n\n<pre><code>create function [dbo].[Age](@dayOfBirth datetime, @today datetime)\n   RETURNS varchar(100)\nAS\n\nBegin\nDECLARE @thisYearBirthDay datetime\nDECLARE @years int, @months int, @days int\n\nset @thisYearBirthDay = DATEADD(year, DATEDIFF(year, @dayOfBirth, @today), @dayOfBirth)\nset @years = DATEDIFF(year, @dayOfBirth, @today) - (CASE WHEN @thisYearBirthDay &gt; @today THEN 1 ELSE 0 END)\nset @months = MONTH(@today - @thisYearBirthDay) - 1\nset @days = DAY(@today - @thisYearBirthDay) - 1\n\nreturn cast(@years as varchar(2)) + ' years,' + cast(@months as varchar(2)) + ' months,' + cast(@days as varchar(3)) + ' days'\nend\n</code></pre>\n"}, {'answer_id': 3324468, 'author': 'sumesh', 'author_id': 400953, 'author_profile': 'https://Stackoverflow.com/users/400953', 'pm_score': 2, 'selected': False, 'text': "<pre><code>create  procedure getDatedifference\n\n(\n    @startdate datetime,\n    @enddate datetime\n)\nas\nbegin\n    declare @monthToShow int\n    declare @dayToShow int\n\n    --set @startdate='01/21/1934'\n    --set @enddate=getdate()\n\n    if (DAY(@startdate) &gt; DAY(@enddate))\n        begin\n            set @dayToShow=0\n\n            if (month(@startdate) &gt; month(@enddate))\n                begin\n                    set @monthToShow=  (12-month(@startdate)+ month(@enddate)-1)\n                end\n            else if (month(@startdate) &lt; month(@enddate))\n                begin\n                    set @monthToShow=  ((month(@enddate)-month(@startdate))-1)\n                end\n            else\n               begin\n                   set @monthToShow=  11\n               end\n            -- 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\n                         if(@monthToShow&lt;0)\n                         begin\n                            set @monthToShow=0\n                         end\n\n                      declare @amonthbefore integer\n                      set @amonthbefore=Month(@enddate)-1\n                          if(@amonthbefore=0)\n                             begin\n                                set @amonthbefore=12\n                              end\n\n\n                      if (@amonthbefore  in(1,3,5,7,8,10,12))\n                          begin\n                            set @dayToShow=31-DAY(@startdate)+DAY(@enddate)\n                          end\n                      if (@amonthbefore=2)\n                         begin\n                           IF (YEAR( @enddate ) % 4 = 0 AND YEAR( @enddate ) % 100 != 0) OR  YEAR( @enddate ) % 400 = 0\n                                 begin\n                                    set @dayToShow=29-DAY(@startdate)+DAY(@enddate)\n                                  end\n                           else\n                               begin\n                                   set @dayToShow=28-DAY(@startdate)+DAY(@enddate)\n                           end\n                      end\n                      if (@amonthbefore in (4,6,9,11))\n                        begin\n                           set @dayToShow=30-DAY(@startdate)+DAY(@enddate)\n                        end\n                 end\n    else\n        begin\n          --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))\n          if (month(@enddate)&lt; month(@startdate))\n              begin\n                 set @monthToShow=12+(month(@enddate)-month(@startdate))\n              end\n          else\n              begin\n                set @monthToShow= (month(@enddate)-month(@startdate))\n              end\n          set @dayToShow=DAY(@enddate)-DAY(@startdate)\n        end\n\n    SELECT\n        FLOOR(DATEDIFF(day, @startdate, @enddate) / 365.25) as [yearToShow],\n          @monthToShow as  monthToShow ,@dayToShow as dayToShow ,\n        convert(varchar,FLOOR(DATEDIFF(day, @startdate, @enddate) / 365.25)) +' Year ' + convert(varchar,@monthToShow) +' months '+convert(varchar,@dayToShow)+' days ' as age\n\n    return\nend\n</code></pre>\n"}, {'answer_id': 6618627, 'author': 'tkerwood', 'author_id': 463425, 'author_profile': 'https://Stackoverflow.com/users/463425', 'pm_score': 4, 'selected': False, 'text': '<p>Try this...</p>\n\n<pre><code>SELECT CASE WHEN\n (DATEADD(year,DATEDIFF(year, @datestart  ,@dateend) , @datestart) &gt; @dateend)\nTHEN DATEDIFF(year, @datestart  ,@dateend) -1\nELSE DATEDIFF(year, @datestart  ,@dateend)\nEND\n</code></pre>\n\n<p>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. </p>\n'}, {'answer_id': 8312825, 'author': 'Keith', 'author_id': 1071528, 'author_profile': 'https://Stackoverflow.com/users/1071528', 'pm_score': 1, 'selected': False, 'text': '<p>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).\nI welcome feedback on this function.  Might not still need a little adjusting.</p>\n\n<p>-- Input to the function is two dates.\n-- Output is the numeric number of years between the two dates in Decimal(7,4) format.\n-- Output is always always a possitive number. </p>\n\n<h2>-- NOTE:Output does not handle if difference is greater than 999.9999</h2>\n\n<p>-- Logic is based on three steps.\n-- 1) Is the difference less than 1 year (0.5000, 0.3333, 0.6667, ect.)\n-- 2) Is the difference exactly a whole number of years (1,2,3, ect.)</p>\n\n<h2>-- 3) (Else)...The difference is years and some number of days. (1.5000, 2.3333, 7.6667, ect.)</h2>\n\n<hr>\n\n<hr>\n\n<pre><code>CREATE Function [dbo].[F_Get_Actual_Age](@pi_date1 datetime,@pi_date2 datetime)\nRETURNS Numeric(7,4)\nAS\nBEGIN\n\nDeclare \n @l_tmp_date    DATETIME\n,@l_days1       DECIMAL(9,6)\n,@l_days2       DECIMAL(9,6)\n,@l_result      DECIMAL(10,6)\n,@l_years       DECIMAL(7,4)\n\n\n  --Check to make sure there is a date for both inputs\n  IF @pi_date1 IS NOT NULL and @pi_date2 IS NOT NULL  \n  BEGIN\n\n    IF @pi_date1 &gt; @pi_date2 --Make sure the "older" date is in @pi_date1\n      BEGIN\n        SET @l_tmp_date = @pi_date2\n        SET @pi_date2 = @Pi_date1\n        SET @pi_date1 = @l_tmp_date\n      END\n\n    --Check #1 If date1 + 1 year is greater than date2, difference must be less than 1 year\n    IF DATEADD(YYYY,1,@pi_date1) &gt; @pi_date2  \n      BEGIN\n          --How many days between the two dates (numerator)\n        SET @l_days1 = DATEDIFF(dd,@pi_date1, @pi_date2) \n          --subtract 1 year from date2 and calculate days bewteen it and date2\n          --This is to get the denominator and accounts for leap year (365 or 366 days)\n        SET @l_days2 = DATEDIFF(dd,dateadd(yyyy,-1,@pi_date2),@pi_date2) \n        SET @l_years = @l_days1 / @l_days2 -- Do the math\n      END\n    ELSE\n      --Check #2  Are the dates an exact number of years apart.\n      --Calculate years bewteen date1 and date2, then add the years to date1, compare dates to see if exactly the same.\n      IF DATEADD(YYYY,DATEDIFF(YYYY,@pi_date1,@pi_date2),@pi_date1) = @pi_date2  \n        SET @l_years = DATEDIFF(YYYY,@pi_date1, @pi_date2) --AS Years, \'Exactly even Years\' AS Msg\n      ELSE\n      BEGIN\n        --Check #3 The rest of the cases.\n        --Check if datediff, returning years, over or under states the years difference\n        SET @l_years = DATEDIFF(YYYY,@pi_date1, @pi_date2)\n        IF DATEADD(YYYY,@l_years,@pi_date1) &gt; @pi_date2\n          SET @l_years = @l_years -1\n          --use basicly same logic as in check #1  \n        SET @l_days1 = DATEDIFF(dd,DATEADD(YYYY,@l_years,@pi_date1), @pi_date2) \n        SET @l_days2 = DATEDIFF(dd,dateadd(yyyy,-1,@pi_date2),@pi_date2) \n        SET @l_years = @l_years + @l_days1 / @l_days2\n        --SELECT @l_years AS Years, \'Years Plus\' AS Msg\n      END\n  END\n  ELSE\n    SET @l_years = 0  --If either date was null\n\nRETURN @l_Years  --Return the result as decimal(7,4)\nEND  \n</code></pre>\n\n<p>`</p>\n'}, {'answer_id': 9986532, 'author': 'Will', 'author_id': 377058, 'author_profile': 'https://Stackoverflow.com/users/377058', 'pm_score': 0, 'selected': False, 'text': "<p><code>DateTime</code> 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.</p>\n\n<pre><code>declare @birthdate datetime\nset @birthdate = '6/15/1974'\n\n--age in years - short version\nprint year(getdate() - @birthdate) - year(0)\n\n--age in years - visualization\ndeclare @mindate datetime\ndeclare @span datetime\n\nset @mindate = 0\nset @span = getdate() - @birthdate\n\nprint @mindate\nprint @birthdate\nprint getdate()\nprint @span\n--substract minyear from spanyear to get age in years\nprint year(@span) - year(@mindate)\nprint month(@span)\nprint day(@span)\n</code></pre>\n"}, {'answer_id': 11169287, 'author': 'Md. Munir Hussain', 'author_id': 1476734, 'author_profile': 'https://Stackoverflow.com/users/1476734', 'pm_score': 0, 'selected': False, 'text': "<p>Here is SQL code that gives you the number of years, months, and days since the sysdate. \nEnter 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</p>\n\n<pre><code>select trunc((sysdate -to_date('&amp;input_birth_date_dd_mon_yy'))/365) years,\ntrunc(mod(( sysdate -to_date('&amp;input_birth_date_dd_mon_yy'))/365,1)*12) months,\ntrunc((mod((mod((sysdate -to_date('&amp;input_birth_date_dd_mon_yy'))/365,1)*12),1)*30)+1) days \n from dual\n</code></pre>\n"}, {'answer_id': 15061160, 'author': 'Junaid', 'author_id': 2106258, 'author_profile': 'https://Stackoverflow.com/users/2106258', 'pm_score': 0, 'selected': False, 'text': "<pre><code>CREATE FUNCTION DBO.GET_AGE\n(\n@DATE AS DATETIME\n)\nRETURNS VARCHAR(MAX)\nAS\nBEGIN\n\nDECLARE @YEAR  AS VARCHAR(50) = ''\nDECLARE @MONTH AS VARCHAR(50) = ''\nDECLARE @DAYS  AS VARCHAR(50) = ''\nDECLARE @RESULT AS VARCHAR(MAX) = ''\n\nSET @YEAR  = CONVERT(VARCHAR,(SELECT DATEDIFF(MONTH,CASE WHEN DAY(@DATE) &gt; DAY(GETDATE()) THEN DATEADD(MONTH,1,@DATE) ELSE @DATE END,GETDATE()) / 12 ))\nSET @MONTH = CONVERT(VARCHAR,(SELECT DATEDIFF(MONTH,CASE WHEN DAY(@DATE) &gt; DAY(GETDATE()) THEN DATEADD(MONTH,1,@DATE) ELSE @DATE END,GETDATE()) % 12 ))\nSET @DAYS = DATEDIFF(DD,DATEADD(MM,CONVERT(INT,CONVERT(INT,@YEAR)*12 + CONVERT(INT,@MONTH)),@DATE),GETDATE())\n\nSET @RESULT = (RIGHT('00' + @YEAR, 2) + ' YEARS ' + RIGHT('00' + @MONTH, 2) + ' MONTHS ' + RIGHT('00' + @DAYS, 2) + ' DAYS')\n\nRETURN @RESULT\nEND\n\nSELECT DBO.GET_AGE('04/12/1986')\n</code></pre>\n"}, {'answer_id': 17064482, 'author': 'ZafarYousafi', 'author_id': 134164, 'author_profile': 'https://Stackoverflow.com/users/134164', 'pm_score': 1, 'selected': False, 'text': "<p>Quite Old question, but I want to share what I have done to calculate age</p>\n\n<pre><code>    Declare @BirthDate As DateTime\nSet @BirthDate = '1994-11-02'\n\nSELECT DATEDIFF(YEAR,@BirthDate,GETDATE()) - (CASE \nWHEN MONTH(@BirthDate)&gt; MONTH(GETDATE()) THEN 1 \nWHEN MONTH(@BirthDate)= MONTH(GETDATE()) AND DAY(@BirthDate) &gt; DAY(GETDATE()) THEN 1 \nElse 0 END)\n</code></pre>\n"}, {'answer_id': 18517263, 'author': 'user2730262', 'author_id': 2730262, 'author_profile': 'https://Stackoverflow.com/users/2730262', 'pm_score': 0, 'selected': False, 'text': '<pre><code>DECLARE @BirthDate datetime, @AgeInMonths int\nSET @BirthDate = \'10/5/1971\'\nSET @AgeInMonths                              -- Determine the age in "months old":\n    = DATEDIFF(MONTH, @BirthDate, GETDATE())  -- .Get the difference in months\n    - CASE WHEN DATEPART(DAY,GETDATE())       -- .If today was the 1st to 4th,\n              &lt; DATEPART(DAY,@BirthDate)      --   (or before the birth day of month)\n           THEN 1 ELSE 0 END                  --   ... don\'t count the month.\nSELECT @AgeInMonths / 12 as AgeYrs            -- Divide by 12 months to get the age in years\n      ,@AgeInMonths % 12 as AgeXtraMonths     -- Get the remainder of dividing by 12 months = extra months\n      ,DATEDIFF(DAY                           -- For the extra days, find the difference between, \n               ,DATEADD(MONTH, @AgeInMonths   -- 1. Last Monthly Birthday \n                             , @BirthDate)    --     (if birthdays were celebrated monthly)\n               ,GETDATE()) as AgeXtraDays     -- 2. Today\'s date.\n</code></pre>\n'}, {'answer_id': 19204768, 'author': 'Ajit Bhgayanathan', 'author_id': 2850876, 'author_profile': 'https://Stackoverflow.com/users/2850876', 'pm_score': 4, 'selected': False, 'text': "<p>Simple way to get age as text is as below:</p>\n\n<pre><code>Select cast((DATEDIFF(m, date_of_birth, GETDATE())/12) as varchar) + ' Y &amp; ' + \n       cast((DATEDIFF(m, date_of_birth, GETDATE())%12) as varchar) + ' M' as Age\n</code></pre>\n\n<p>Results Format will be:</p>\n\n<pre><code>**63 Y &amp; 2 M**\n</code></pre>\n"}, {'answer_id': 25912864, 'author': 'Jaugar Chang', 'author_id': 3630826, 'author_profile': 'https://Stackoverflow.com/users/3630826', 'pm_score': 3, 'selected': False, 'text': '<h2>Implemented by arithmetic with ISO formatted date.</h2>\n\n<pre><code>declare @now date,@dob date, @now_i int,@dob_i int, @days_in_birth_month int\ndeclare @years int, @months int, @days int\nset @now = \'2013-02-28\' \nset @dob = \'2012-02-29\' -- Date of Birth\n\nset @now_i = convert(varchar(8),@now,112) -- iso formatted: 20130228\nset @dob_i = convert(varchar(8),@dob,112) -- iso formatted: 20120229\nset @years = ( @now_i - @dob_i)/10000\n-- (20130228 - 20120229)/10000 = 0 years\n\nset @months =(1200 + (month(@now)- month(@dob))*100 + day(@now) - day(@dob))/100 %12\n-- (1200 + 0228 - 0229)/100 % 12 = 11 months\n\nset @days_in_birth_month = day(dateadd(d,-1,left(convert(varchar(8),dateadd(m,1,@dob),112),6)+\'01\'))\nset @days = (sign(day(@now) - day(@dob))+1)/2 * (day(@now) - day(@dob))\n          + (sign(day(@dob) - day(@now))+1)/2 * (@days_in_birth_month - day(@dob) + day(@now))\n-- ( (-1+1)/2*(28 - 29) + (1+1)/2*(29 - 29 + 28))\n-- Explain: if the days of now is bigger than the days of birth, then diff the two days\n--          else add the days of now and the distance from the date of birth to the end of the birth month \nselect @years,@months,@days -- 0, 11, 28 \n</code></pre>\n\n<h2>Test Cases</h2>\n\n<p><em>The approach of days is different from the accepted answer, the differences shown in the comments below:</em></p>\n\n<pre><code>       dob        now  years  months  days \n2012-02-29 2013-02-28      0      11    28  --Days will be 30 if calculated by the approach in accepted answer. \n2012-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. \n2012-02-29 2016-03-31      4       1     2\n2012-01-30 2016-02-29      4       0    30\n2012-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.\n2011-12-30 2016-02-29      4       1    30\n</code></pre>\n\n<h2>An short version of Days by case statement:</h2>\n\n<pre><code>set @days = CASE WHEN day(@now) &gt;= day(@dob) THEN day(@now) - day(@dob)\n                 ELSE @days_in_birth_month - day(@dob) + day(@now) END\n</code></pre>\n\n<p><strong>If you want the age of years and months only, it could be simpler</strong></p>\n\n<pre><code>set @years = ( @now_i/100 - @dob_i/100)/100\nset @months =(12 + month(@now) - month(@dob))%12 \nselect @years,@months -- 1, 0\n</code></pre>\n\n<p><strong>NOTE:</strong> A very useful link of <a href="http://www.sql-server-helper.com/tips/date-formats.aspx" rel="noreferrer">SQL Server Date Formats</a></p>\n'}, {'answer_id': 27064788, 'author': 'miguelbgouveia', 'author_id': 2486661, 'author_profile': 'https://Stackoverflow.com/users/2486661', 'pm_score': 0, 'selected': False, 'text': '<p>For the ones that want to create a calculated column in a table to store the age:</p>\n\n<pre><code>CASE WHEN DateOfBirth&lt; DATEADD(YEAR, (DATEPART(YEAR, GETDATE()) - DATEPART(YEAR, DateOfBirth))*-1, GETDATE()) \n     THEN DATEPART(YEAR, GETDATE()) - DATEPART(YEAR, DateOfBirth)\n     ELSE DATEPART(YEAR, GETDATE()) - DATEPART(YEAR, DateOfBirth) -1 END\n</code></pre>\n'}, {'answer_id': 35181398, 'author': 'Komengem', 'author_id': 619010, 'author_profile': 'https://Stackoverflow.com/users/619010', 'pm_score': 0, 'selected': False, 'text': "<p>Here is how I calculate the age given a birth date and the current date.</p>\n\n<pre><code>select case \n            when cast(getdate() as date) = cast(dateadd(year, (datediff(year, '1996-09-09', getdate())), '1996-09-09') as date)\n                then dateDiff(yyyy,'1996-09-09',dateadd(year, 0, getdate()))\n            else dateDiff(yyyy,'1996-09-09',dateadd(year, -1, getdate()))\n        end as MemberAge\ngo\n</code></pre>\n"}, {'answer_id': 35902737, 'author': 'Mark Brittingham', 'author_id': 15592, 'author_profile': 'https://Stackoverflow.com/users/15592', 'pm_score': 0, 'selected': False, 'text': "<p>There is an easy way, based on the hours between the two days BUT with the end date truncated. </p>\n\n<pre><code>SELECT CAST(DATEDIFF(hour,Birthdate,CAST(GETDATE() as Date))/8766.0 as INT) AS Age FROM &lt;YourTable&gt;\n</code></pre>\n\n<p>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 <strong>exactly</strong> midnight.</p>\n"}, {'answer_id': 36428043, 'author': 'Harry Steinmeyer', 'author_id': 6157545, 'author_profile': 'https://Stackoverflow.com/users/6157545', 'pm_score': -1, 'selected': False, 'text': "<pre><code>DECLARE @DoB AS DATE = '1968-10-24'\nDECLARE @cDate AS DATE = CAST('2000-10-23' AS DATE)\n\nSELECT \n--Get Year difference\nDATEDIFF(YEAR,@DoB,@cDate) -\n--Cases where year difference will be augmented\nCASE \n    --If Date of Birth greater than date passed return 0\n    WHEN YEAR(@DoB) - YEAR(@cDate) &gt;= 0 THEN DATEDIFF(YEAR,@DoB,@cDate)\n\n    --If date of birth month less than date passed subtract one year\n    WHEN MONTH(@DoB) - MONTH(@cDate) &gt; 0 THEN 1 \n\n    --If date of birth day less than date passed subtract one year\n    WHEN MONTH(@DoB) - MONTH(@cDate) = 0 AND DAY(@DoB) - DAY(@cDate) &gt; 0 THEN 1 \n\n    --All cases passed subtract zero\n    ELSE 0\nEND\n</code></pre>\n"}, {'answer_id': 37345569, 'author': 'user6360847', 'author_id': 6360847, 'author_profile': 'https://Stackoverflow.com/users/6360847', 'pm_score': -1, 'selected': False, 'text': "<pre><code>declare @StartDate datetime = '2016-01-31'\ndeclare @EndDate datetime = '2016-02-01'\nSELECT @StartDate AS [StartDate]\n      ,@EndDate AS [EndDate]\n      ,DATEDIFF(Year,@StartDate,@EndDate) - CASE WHEN DATEADD(Year,DATEDIFF(Year,@StartDate,@EndDate), @StartDate) &gt; @EndDate THEN 1 ELSE 0 END AS [Years]\n      ,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]\n      ,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]\n</code></pre>\n"}, {'answer_id': 37915012, 'author': 'Prince Jain', 'author_id': 6487482, 'author_profile': 'https://Stackoverflow.com/users/6487482', 'pm_score': -1, 'selected': False, 'text': '<pre><code>select DOB as Birthdate,\n       YEAR(GETDATE()) as ThisYear, \n       YEAR(getdate()) - EAR(date1) as Age   \nfrom TableName\n</code></pre>\n'}, {'answer_id': 37915082, 'author': 'Prince Jain', 'author_id': 6487482, 'author_profile': 'https://Stackoverflow.com/users/6487482', 'pm_score': -1, 'selected': False, 'text': '<pre><code>SELECT DOB AS Birthdate ,\n       YEAR(GETDATE()) AS ThisYear,\n       YEAR(getdate()) - YEAR(DOB) AS Age\nFROM tableprincejain\n</code></pre>\n'}, {'answer_id': 51813114, 'author': 'Adal H. Vega', 'author_id': 2097023, 'author_profile': 'https://Stackoverflow.com/users/2097023', 'pm_score': 2, 'selected': False, 'text': '<p>I use this Function I modified (the Days part) From @Dane answer: <a href="https://stackoverflow.com/a/57720/2097023">https://stackoverflow.com/a/57720/2097023</a></p>\n\n<pre><code>CREATE FUNCTION dbo.EdadAMD\n    (\n        @FECHA DATETIME\n    )\n    RETURNS NVARCHAR(10)\n    AS\n    BEGIN\n        DECLARE\n            @tmpdate DATETIME\n          , @years   INT\n          , @months  INT\n          , @days    INT\n          , @EdadAMD NVARCHAR(10);\n\n        SELECT @tmpdate = @FECHA;\n\n        SELECT @years = DATEDIFF(yy, @tmpdate, GETDATE()) - CASE\n                                          WHEN (MONTH(@FECHA) &gt;    MONTH(GETDATE()))\n                                             OR (\n                                                MONTH(@FECHA) = MONTH(GETDATE())\n                                          AND DAY(@FECHA) &gt; DAY(GETDATE())\n                                          ) THEN\n                                                1\n                                            ELSE\n                                                0\n                                    END;\n    SELECT @tmpdate = DATEADD(yy, @years, @tmpdate);\n    SELECT @months = DATEDIFF(m, @tmpdate, GETDATE()) - CASE\n                              WHEN DAY(@FECHA) &gt; DAY(GETDATE()) THEN\n                                                            1\n                                                        ELSE\n                                                            0\n                                                    END;\n    SELECT @tmpdate = DATEADD(m, @months, @tmpdate);\n\n    IF MONTH(@FECHA) = MONTH(GETDATE())\n       AND DAY(@FECHA) &gt; DAY(GETDATE())\n          SELECT @days = \n            DAY(EOMONTH(GETDATE(), -1)) - (DAY(@FECHA) - DAY(GETDATE()));\n    ELSE\n        SELECT @days = DATEDIFF(d, @tmpdate, GETDATE());\n\n    SELECT @EdadAMD = CONCAT(@years, \'a\', @months, \'m\', @days, \'d\');\n\n    RETURN @EdadAMD;\n\nEND; \nGO\n</code></pre>\n\n<p>It works pretty well.</p>\n'}, {'answer_id': 54178003, 'author': 'Sai Krishnan Harish', 'author_id': 7906457, 'author_profile': 'https://Stackoverflow.com/users/7906457', 'pm_score': 0, 'selected': False, 'text': '<p>There is another method for calculate age is</p>\n\n<p>See below table</p>\n\n<pre><code>    FirstName       LastName    DOB\n    sai             krishnan    1991-11-04\n    Harish          S A         1998-10-11\n</code></pre>\n\n<p>For finding age,you can calculate through month</p>\n\n<pre><code>  Select datediff(MONTH,DOB,getdate())/12 as dates from [Organization].[Employee]\n</code></pre>\n\n<p>Result will be </p>\n\n<pre><code>firstname   dates\nsai         27\nHarish      20\n</code></pre>\n'}, {'answer_id': 59627199, 'author': 'Sanket Doshi', 'author_id': 5934456, 'author_profile': 'https://Stackoverflow.com/users/5934456', 'pm_score': -1, 'selected': False, 'text': "<pre><code>declare @BirthDate datetime\ndeclare @TotalYear int\ndeclare @TotalMonths int\ndeclare @TotalDays int\ndeclare @TotalWeeks int\ndeclare @TotalHours int\ndeclare @TotalMinute int\ndeclare @TotalSecond int\ndeclare @CurrentDtTime datetime\nset @BirthDate='1998/01/05 05:04:00'  -- Set Your date here\nset @TotalYear= FLOOR(DATEDIFF(DAY, @BirthDate, GETDATE()) / 365.25)\nset @TotalMonths= FLOOR(DATEDIFF(DAY,DATEADD(year, @TotalYear,@BirthDate),GetDate()) / 30.436875E)\nset @TotalDays= FLOOR(DATEDIFF(DAY, DATEADD(month, @TotalMonths,DATEADD(year, \n    @TotalYear,@BirthDate)), GETDATE()))\nset @CurrentDtTime=CONVERT(datetime,CONVERT(varchar(50), DATEPART(year, \n    GetDate()))+'/' +CONVERT(varchar(50), DATEPART(MONTH, GetDate()))\n    +'/'+ CONVERT(varchar(50),DATEPART(DAY, GetDate()))+' '\n    + CONVERT(varchar(50),DATEPART(HOUR, @BirthDate))+':'+ \n     CONVERT(varchar(50),DATEPART(MINUTE, @BirthDate))+\n   ':'+ CONVERT(varchar(50),DATEPART(Second, @BirthDate)))\nset @TotalHours = DATEDIFF(hour, @CurrentDtTime, GETDATE())\nif(@TotalHours &lt; 0)\nbegin\n   set @TotalHours = DATEDIFF(hour,DATEADD(Day,-1, @CurrentDtTime), GETDATE())\n   set @TotalDays= @TotalDays -1  \n end\nset @TotalMinute= DATEPART(MINUTE, GETDATE())-DATEPART(MINUTE, @BirthDate)\n if(@TotalMinute &lt; 0)\nset @TotalMinute = DATEPART(MINUTE, DATEADD(hour,-1,GETDATE()))+(60-DATEPART(MINUTE, \n   @BirthDate))\n\nset @TotalSecond= DATEPART(Second, GETDATE())-DATEPART(Second, @BirthDate)\n\n Print 'Your age are'+ CHAR(13)\n + CONVERT(varchar(50), @TotalYear)+' Years, ' +\n   CONVERT(varchar(50),@TotalMonths) +' Months, ' +\n   CONVERT(varchar(50),@TotalDays)+' Days, ' +\n   CONVERT(varchar(50),@TotalHours)+' Hours, ' +\n   CONVERT(varchar(50),@TotalMinute)+' Minutes, ' + \n   CONVERT(varchar(50),@TotalSecond)+' Seconds. ' +char(13)+\n     'Your are born at day of week was - ' + CONVERT(varchar(50),DATENAME(dw , \n     @BirthDate ))\n  +char(13)+char(13)+\n+'Your Birthdate to till date your '+ CHAR(13)\n+'Years - ' + CONVERT(varchar(50), FLOOR(DATEDIFF(DAY, @BirthDate, GETDATE()) / \n   365.25))\n+' , Months - ' + CONVERT(varchar(50),DATEDIFF(MM,@BirthDate,getdate())) \n+' , Weeks - ' + CONVERT(varchar(50),DATEDIFF(wk,@BirthDate,getdate()))\n+' , Days - ' + CONVERT(varchar(50),DATEDIFF(dd,@BirthDate,getdate()))+char(13)+\n+'Hours - ' + CONVERT(varchar(50),DATEDIFF(HH,@BirthDate,getdate()))\n+' , Minutes - ' + CONVERT(varchar(50),DATEDIFF(mi,@BirthDate,getdate()))\n+' , Seconds - ' + CONVERT(varchar(50),DATEDIFF(ss,@BirthDate,getdate()))\n</code></pre>\n\n<p>Output</p>\n\n<pre><code>Your age are\n22 Years, 0 Months, 2 Days, 11 Hours, 30 Minutes, 16 Seconds. \nYour are born at day of week was - Monday\n\nYour Birthdate to till date your \nYears - 22 , Months - 264 , Weeks - 1148 , Days - 8037\nHours - 192899 , Minutes - 11573970 , Seconds - 694438216\n</code></pre>\n"}, {'answer_id': 73035178, 'author': 'Mohan Kethireddigari', 'author_id': 19533588, 'author_profile': 'https://Stackoverflow.com/users/19533588', 'pm_score': 0, 'selected': False, 'text': '<p>I have created a function <code>calculateAge</code> that takes parameter <code>dateOfBirth</code> from outside and then it calculates the age in years, months and days and finally it returns in string format.</p>\n<pre><code>CREATE FUNCTION calculateAge(dateOfBirth datetime) RETURNS varchar(40)\nBEGIN\n    set @currentdatetime = CURRENT_TIMESTAMP;\n    set @years = TIMESTAMPDIFF(YEAR,dateOfBirth,@currentdatetime);\n    set @months = TIMESTAMPDIFF(MONTH,dateOfBirth,@currentdatetime) - @years*12 ;\n    set @dayOfBirth = EXTRACT(DAY FROM dateOfBirth);\n    set @today = EXTRACT(DAY FROM @currentdatetime);\n    set @days = 0;\n    if (@today &gt; @dayOfBirth) then\n        set @days = @today - @dayOfBirth;\n    else\n        set @decreaseMonth = DATE_SUB(@currentdatetime, INTERVAL 1 MONTH);\n        set @days = DATEDIFF(dateOfBirth, @decreaseMonth);\n    end if;\n    RETURN concat(concat( concat(@years , &quot;years\\n&quot;) , concat(@months , &quot;months\\n&quot;)), concat(@days , &quot;days&quot;));\nEND\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/57599', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/845/']