How To Display The Exact Age In Year Month Day Format In Sql Server

I am stuck with my query. I have a table called Patient. In this table a column has patient DOB. Actually I want to display the exact age of the patient. For example: PatName DOB

Solution 1:

Hi Check the query below.

--DROP TABLE patientCREATETABLE patient(PatName varchar(100),DOB date,  Age varchar(100))
INSERTINTO patient
VALUES('a','06/02/1947',NULL),('b','07/10/1947',NULL),('c','12/21/1982',NULL)

;WITH CTE(PatName,DOB,years,months,days) AS 
(SELECT PatName,DOB,DATEDIFF(yy,DOB,getdate()),DATEDIFF(mm,DOB,getdate()),DATEDIFF(dd,DOB,getdate()) FROM patient)

--SELECT * FROM CTESELECT PatName,DOB,
      CAST(months/12asvarchar(5))+' Years'+CAST((months %12) asvarchar(5))+' month/s '+CAST(CASEWHEN DATEADD(MM,(months %12),DATEADD(YY,(months/12),DOB)) <= GETDATE() then DATEDIFF(dd,DATEADD(MM,(months %12),DATEADD(YY,(months/12),DOB)),GETDATE()) ELSEDAY(getdate()) ENDasvarchar(5))+' days'as Age

FROM CTE

Solution 2:

If you follow this link, you'll find a function doing exactly that:

createfunction dbo.F_AGE_YYYY_MM_DD
    (
    @START_DATE     datetime,
    @END_DATE       datetime
    )
returnsvarchar(10) 
as/*
Function: F_AGE_YYYY_MM_DD

This function calculates age in years, months and days
from @START_DATE through @END_DATE and
returns the age in format YYYY MM DD.

Years is the number of full years between @START_DATE and @END_DATE.

Months is the number of full months since the last full year anniversary.

Days is the number of days since the last full month anniversary.

*/begindeclare@AGEvarchar(10)

declare@AGE_IN_YEARS       intdeclare@AGE_IN_MONTHS  intdeclare@AGE_IN_DAYS        int-- Return null if @START_DATE > @END_DATE
if @START_DATE >@END_DATE beginreturn@AGEendselect@AGE_IN_YEARS = AGE_IN_YEARS,
    @AGE_IN_MONTHS = AGE_IN_MONTHS,
    @AGE_IN_DAYS =
        datediff(dd,
        dateadd(mm,AGE_IN_MONTHS,
        dateadd(yy,AGE_IN_YEARS,START_DATE))
        ,END_DATE)
from
(
select
    AGE_IN_MONTHS =casewhen AnniversaryThisMonth <= END_DATE
    then datediff(mm,dateadd(yy,AGE_IN_YEARS,START_DATE),END_DATE)
    else datediff(mm,dateadd(yy,AGE_IN_YEARS,START_DATE),END_DATE)-1end,
    *from
(
select
    AGE_IN_YEARS =casewhen AnniversaryThisYear <= END_DATE
    then datediff(yy,START_DATE,END_DATE)
    else datediff(yy,START_DATE,END_DATE)-1end,
    *from
(
select
    AnniversaryThisYear =
    dateadd(yy,datediff(yy,START_DATE,END_DATE),START_DATE),
    AnniversaryThisMonth =
    dateadd(mm,datediff(mm,START_DATE,END_DATE),START_DATE),
    *from
(
select  START_DATE  = dateadd(dd,datediff(dd,0,@START_DATE),0),
    END_DATE    = dateadd(dd,datediff(dd,0,@END_DATE),0)
) aaaa
) aaa
) aa
) a

select@AGE=right('0000'+convert(varchar(4),@AGE_IN_YEARS),4) +' '+right('00'+convert(varchar(4),@AGE_IN_MONTHS),2) +' '+right('00'+convert(varchar(4),@AGE_IN_DAYS),2)

return@AGEend
go

select [Age] = dbo.F_AGE_YYYY_MM_DD('2004-04-07','2006-02-03')

select [Age] = dbo.F_AGE_YYYY_MM_DD('2006-02-03','2006-02-03')

select [Age] = dbo.F_AGE_YYYY_MM_DD('2006-02-05','2006-02-03')

select [Age] = dbo.F_AGE_YYYY_MM_DD('1950-09-13', getdate())

Solution 3:

DECLARE@date datetime, @tmpdate datetime, @yearsint, @monthsint, @daysintSELECT@date='06/02/1947'SELECT@tmpdate=@dateSELECT@years= DATEDIFF(yy, @tmpdate, GETDATE()) -CASEWHEN (MONTH(@date) >MONTH(GETDATE())) OR (MONTH(@date) =MONTH(GETDATE()) ANDDAY(@date) >DAY(GETDATE())) THEN1ELSE0ENDSELECT@tmpdate= DATEADD(yy, @years, @tmpdate)
SELECT@months= DATEDIFF(m, @tmpdate, GETDATE()) -CASEWHENDAY(@date) >DAY(GETDATE()) THEN1ELSE0ENDSELECT@tmpdate= DATEADD(m, @months, @tmpdate)
SELECT@days= DATEDIFF(d, @tmpdate, GETDATE())

print cast(@yearsasvarchar(4)) +' years, '+cast (@monthsasvarchar(2))+' months, '+cast(@daysasvarchar(2)) +' days'

Solution 4:

Here is the solution

DATEDIFF(year, DOB, getdate()) - (CASE WHEN (DATEADD(year, DATEDIFF(year, DOB, getdate()), DOB)) > getdate() THEN 1 ELSE 0 END) as Years, 
MONTH(getdate() - (DATEADD(year, DATEDIFF(year, DOB, getdate()), DOB))) - 1 as Month/s, 
DAY(getdate() - (DATEADD(year, DATEDIFF(year, DOB, getdate()), DOB))) - 1 as Days