CODEWITHSUNDEEP
sqlsql-server
Crezzer7
Crezzer7

Reputation: 2335

SQL Server - calculate age in day, month, year in select query

I have found a couple of examples that have helped me to break this down, however none that can be used on a table of data.

I have a list of names and dates in a table, and I want to calculate how old each person in in terms of the day, month and year from today, for example:

   Name          DOB        Years      Months      Days
Joe Bloggs     01/10/2012     4          0         11
Steven Wright  29/02/2004     12         7         13

This is the query I am currently using: 

SELECT
Person.Name,
Person.DOB,
DATEDIFF(yy, Person.DOB, GETDATE()) - CASE WHEN (MONTH(Person.DOB) > MONTH(GETDATE())) OR (MONTH(Person.DOB) = MONTH(GETDATE()) AND DAY(Person.DOB) > DAY(GETDATE())) THEN 1 ELSE 0 END AS Years,
DATEDIFF(m, Person.DOB, GETDATE()) - CASE WHEN DAY(Person.DOB) > DAY(GETDATE()) THEN 1 ELSE 0 END AS Months,
DATEDIFF(d, Person.DOB, GETDATE()) AS Days
FROM Person
Where DOB IS NOT NULL

This will display the following for Joe Bloggs from the SQL Query above:

   Name            DOB        Years      Months      Days
Joe Bloggs      01/10/2012      4          48        1472

Basically what is happening is the years aren't being deducted from the months, and so on.. so this is the total days, total months and total years, instead of them breaking down as shown above.

How can I amend the above query to get this to work using a table of data called Family, with the fields Name and DOB?

Upvotes: 0

Views: 6475

Answers (4)

Charles Bretana
Charles Bretana

Reputation: 146409

How about 

declare @dob date = '22Aug1982'
declare @Yr int = 2016
declare @mon int = 6
declare @dom int = 13
Select @Yr - Year(@dob) - 1 + 
case when @mon > Month(@dob) then 1
     when @mon < Month(@dob) then 0
     else case when @dom >= Day(@dob) then 1 
else 0 end end

But I suggest you add a computed column to the table:

alter table Family
    add AgeYears
    as Year(getdate()) - Year(dob) - 1 +
        case when Month(getdate()) > month(dob) then 1
             when month(getdate()) < month(dob) then 0
             else case when day(getdate()) >= day(dob) then 1
         else 0 end end

If you also need the additional months old, and days old, then add columns for those as well:

alter table Family
    add AgeYears as Year(getdate()) - Year(dob) - 1 +
           case when Month(getdate()) > month(dob) then 1
                when month(getdate()) < month(dob) then 0
                else case when day(getdate()) >= day(dob) then 1
             else 0 end end,
    AgeMonths as case when Month(getdate()) >= Month(dob) 
             then Month(getdate()) - month(dob)
             else month(dob) - Month(getdate()) end,
    AgeDays as case when day(getdate()) >= day(dob) 
             then day(getdate()) - day(dob)
             else day(dob) - day(getdate()) end

Upvotes: 1

Cato
Cato

Reputation: 3701

I TRIED some boundary conditions on this! Seems ok so far have a go if you like.

declare @dob datetime = '19800229';           --date of birth
DECLARE @DAT datetime = '20160301';           --calculation date

SELECT YEAR(@DAT) - YEAR(@dob) - 
            CASE WHEN MONTH(@DAT) < MONTH(@DOB) OR MONTH(@DAT) = MONTH(@DOB) AND DAY(@DAT) < DAY(@DOB) THEN 1 ELSE 0 END YEARS,
        (YEAR(@DAT)*12 - YEAR(@dob)*12 + MONTH(@DAT) - MONTH(@dob) - 
        CASE WHEN DAY(@DAT) < DAY(@DOB) THEN 1 ELSE 0 END) % 12 MONTHS,

    datediff(day,
    dateadd(month,(YEAR(@DAT)*12 - YEAR(@dob)*12 + MONTH(@DAT) - MONTH(@dob) - 
        CASE WHEN DAY(@DAT) < DAY(@DOB) THEN 1 ELSE 0 END) % 12,

    dateadd(year, YEAR(@DAT) - YEAR(@dob) - 
            CASE WHEN MONTH(@DAT) < MONTH(@DOB) OR MONTH(@DAT) = MONTH(@DOB) AND DAY(@DAT) < DAY(@DOB) THEN 1 ELSE 0 END ,@dob)
            ),@DAT) DAYS

Upvotes: 2

Deep
Deep

Reputation: 3202

Try Below solution, I have not tested it thorubut it may work :

DECLARE @DOB DATE = '1991-11-19'
SELECT  DATEDIFF(M,@DOB,GETDATE())/12 [Year],
        DATEDIFF(M,@DOB,GETDATE())%12 [Month],
        DATEDIFF(DD,
        CASE
            WHEN DATEADD(MM,DATEDIFF(MM,@DOB,GETDATE()),@DOB) > GETDATE()
                THEN DATEADD(MM,DATEDIFF(MM,@DOB,GETDATE())-1,@DOB)
            ELSE DATEADD(MM,DATEDIFF(MM,@DOB,GETDATE()),@DOB)
        END,
        GETDATE()) [Day]

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81930

I have at TVF which may look like overkill, but it is very fast and accurate

Declare @YourTable Table (Name varchar(25),DOB Date)
Insert Into @YourTable values
('Joe Bloggs'   ,'2012-10-01'),
('Steven Wright','2004-02-29') 

Select A.*
      ,B.*
 From  @YourTable A
 Cross Apply [dbo].[udf-Date-Elapsed](A.DOB,GetDate()) B

Returns

Name            DOB         Years   Months  Days    Hours   Minutes Seconds
Joe Bloggs      2012-10-01  4       0       11      11      3       17
Steven Wright   2004-02-29  12      7       13      11      3       17

The UDF

CREATE FUNCTION [dbo].[udf-Date-Elapsed] (@D1 DateTime,@D2 DateTime)
Returns Table
Return (
    with cteBN(N)   as (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
         cteRN(R)   as (Select Row_Number() Over (Order By (Select NULL))-1 From cteBN a,cteBN b,cteBN c),
         cteYY(N,D) as (Select Max(R),Max(DateAdd(YY,R,@D1))From cteRN R Where DateAdd(YY,R,@D1)<=@D2),
         cteMM(N,D) as (Select Max(R),Max(DateAdd(MM,R,D))  From (Select Top 12 R From cteRN Order By 1) R, cteYY P Where DateAdd(MM,R,D)<=@D2),
         cteDD(N,D) as (Select Max(R),Max(DateAdd(DD,R,D))  From (Select Top 31 R From cteRN Order By 1) R, cteMM P Where DateAdd(DD,R,D)<=@D2),
         cteHH(N,D) as (Select Max(R),Max(DateAdd(HH,R,D))  From (Select Top 24 R From cteRN Order By 1) R, cteDD P Where DateAdd(HH,R,D)<=@D2),
         cteMI(N,D) as (Select Max(R),Max(DateAdd(MI,R,D))  From (Select Top 60 R From cteRN Order By 1) R, cteHH P Where DateAdd(MI,R,D)<=@D2),
         cteSS(N,D) as (Select Max(R),Max(DateAdd(SS,R,D))  From (Select Top 60 R From cteRN Order By 1) R, cteMI P Where DateAdd(SS,R,D)<=@D2)

    Select [Years]   = cteYY.N
          ,[Months]  = cteMM.N
          ,[Days]    = cteDD.N
          ,[Hours]   = cteHH.N
          ,[Minutes] = cteMI.N
          ,[Seconds] = cteSS.N
     From  cteYY,cteMM,cteDD,cteHH,cteMI,cteSS
)
--Max 1000 years
--Select * from [dbo].[udf-Date-Elapsed] ('1964-07-29 09:35:00.000',GetDate())

Upvotes: 1

Related Questions