如何用年、月、日计算T-SQL的年龄?

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

用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.

datediff函数不能很好地处理年份边界，而且还可以将几个月和几天分开。我知道我可以相对容易地在客户端完成它，但是我想在我的存储过程中完成它。

21 个解决方案

#1

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.

下面是一些T-SQL，它提供了自@date指定日期起的年数、月数和天数。它考虑到DATEDIFF()在不考虑月份或日期的情况下计算差异(因此，8/31和9/1之间的月份差异为1个月)，并处理了一个case语句，在适当的情况下将结果减少。

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) > MONTH(GETDATE())) OR (MONTH(@date) = MONTH(GETDATE()) AND DAY(@date) > DAY(GETDATE())) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)
SELECT @months = DATEDIFF(m, @tmpdate, GETDATE()) - CASE WHEN DAY(@date) > DAY(GETDATE()) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(m, @months, @tmpdate)
SELECT @days = DATEDIFF(d, @tmpdate, GETDATE())

SELECT @years, @months, @days

#2

Try this...

试试这个…

SELECT CASE WHEN
 (DATEADD(year,DATEDIFF(year, @datestart  ,@dateend) , @datestart) > @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.

基本上“DateDiff(年……”，给你这个人今年会转的年龄，所以我刚刚添加了一个case statement说，如果他们今年还没有过生日，然后减1年，不然还会有价值。

#3

Simple way to get age as text is as below:

将年龄作为文本的简单方法如下:

Select cast((DATEDIFF(m, date_of_birth, GETDATE())/12) as varchar) + ' Y & ' + 
       cast((DATEDIFF(m, date_of_birth, GETDATE())%12) as varchar) + ' M' as Age

Results Format will be:

结果的格式将会是:

**63 Y & 2 M**

#4

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 > @today THEN 1 ELSE 0 END)

SELECT @months = MONTH(@today - @thisYearBirthDay) - 1

SELECT @days = DAY(@today - @thisYearBirthDay) - 1

SELECT @years, @months, @days
GO

#5

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 > @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

#6

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) > DAY(@enddate))
        begin
            set @dayToShow=0

            if (month(@startdate) > month(@enddate))
                begin
                    set @monthToShow=  (12-month(@startdate)+ month(@enddate)-1)
                end
            else if (month(@startdate) < 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<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)< 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

#7

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) >= 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

注意:这是一个非常有用的SQL服务器日期格式链接。

#8

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.

——函数的输入是两个日期。——输出是两个日期之间的年份(7,4)的数字。——输出总是一个可能的数字。

-- 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.)

——逻辑基于三个步骤。- 1)是小于1年的差值(0.5000,0.3333,0.6667,ect) - 2)是完全不同的年份(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 > @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) > @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) > @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  

`

”

#9

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)> MONTH(GETDATE()) THEN 1 
WHEN MONTH(@BirthDate)= MONTH(GETDATE()) AND DAY(@BirthDate) > DAY(GETDATE()) THEN 1 
Else 0 END)

#10

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)?

你想计算一个年龄的总天数/月/年吗?你有开始约会吗?或者你试图剖析它(前24年，1个月，29天)?

If you have a start date that you're working with, datediff will output the total days/months/years with the following commands:

如果您有一个开始日期，datediff将会输出以下命令的总天数/月/年:

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).

有相应的输出(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.

现在，如果你想做解剖法，你必须减去数年的天数(天数- 365*年)，然后再做进一步的数学计算来得到月份，等等。

#11

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 & days such as 01-mar-85

这里是SQL代码，它提供了自sysdate以来的年份、月份和天数。输入input_birth_date这个格式的值(dd_mon_yy)。注:输入相同的值(出生日期)数年，月和日，如01-ma -85。

select trunc((sysdate -to_date('&input_birth_date_dd_mon_yy'))/365) years,
trunc(mod(( sysdate -to_date('&input_birth_date_dd_mon_yy'))/365,1)*12) months,
trunc((mod((mod((sysdate -to_date('&input_birth_date_dd_mon_yy'))/365,1)*12),1)*30)+1) days 
 from dual

#12

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.

T-SQL中的DateTime值存储为浮点数。你可以把日期从对方身上减去，你现在有了一个新的日期，这是他们之间的时间间隔。

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)

#13

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) > DAY(GETDATE()) THEN DATEADD(MONTH,1,@DATE) ELSE @DATE END,GETDATE()) / 12 ))
SET @MONTH = CONVERT(VARCHAR,(SELECT DATEDIFF(MONTH,CASE WHEN DAY(@DATE) > 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')

#14

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,
              < 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.

#15

For the ones that want to create a calculated column in a table to store the age:

对于那些想要在表中创建计算列来存储年龄的人:

CASE WHEN DateOfBirth< 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

#16

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 <YourTable>

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.

这个已经被证明是非常准确和可靠的。如果不是在GETDATE()的内部CAST上，它可能会在午夜前几小时翻转生日，但是，随着时间的推移，它会随着年龄的变化而死亡。

#17

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

#18

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) > @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) > @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) > @EndDate THEN 1 ELSE 0 END,@StartDate),@EndDate) , @StartDate) > @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) > @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) > @EndDate THEN 1 ELSE 0 END,@StartDate),@EndDate) , @StartDate) > @EndDate THEN 1 ELSE 0 END  ,DATEADD(Year,DATEDIFF(Year,@StartDate,@EndDate) - CASE WHEN DATEADD(Year,DATEDIFF(Year,@StartDate,@EndDate), @StartDate) > @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) > @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) > @EndDate THEN 1 ELSE 0 END,@StartDate),@EndDate) , @StartDate) > @EndDate THEN 1 ELSE 0 END  ,DATEADD(Year,DATEDIFF(Year,@StartDate,@EndDate) - CASE WHEN DATEADD(Year,DATEDIFF(Year,@StartDate,@EndDate), @StartDate) > @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) > @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) > @EndDate THEN 1 ELSE 0 END,@StartDate),@EndDate) , @StartDate) > @EndDate THEN 1 ELSE 0 END  ,DATEADD(Year,DATEDIFF(Year,@StartDate,@EndDate) - CASE WHEN DATEADD(Year,DATEDIFF(Year,@StartDate,@EndDate), @StartDate) > @EndDate THEN 1 ELSE 0 END,@StartDate))) > @EndDate THEN 1 ELSE 0 END AS [Days]

#19

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

#20

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

#21

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) >= 0 THEN DATEDIFF(YEAR,@DoB,@cDate)

    --If date of birth month less than date passed subtract one year
    WHEN MONTH(@DoB) - MONTH(@cDate) > 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) > 0 THEN 1 

    --All cases passed subtract zero
    ELSE 0
END