Crezzer7 Crezzer7 - 1 month ago 8
SQL Question

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
?

Answer

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())
Comments