SQL Server - 在选择查询中计算日，月，年的年龄

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:

这将从上面的SQL Query中显示Joe Bloggs的以下内容：

   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?

如何使用名为Family的数据表修改上述查询以使其工作，名称和DOB字段？

4 个解决方案

#1

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

我在TVF看起来有点矫枉过正，但它非常快速准确

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

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

#2

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

尝试下面的解决方案，我没有测试它thorubut它可能工作：

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]

#3

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

#4

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