Блог

Я нашел пару примеров, которые помогли мне разобраться в этом, однако ни один из них не может быть использован в таблице данных.

У меня есть список имен и дат в таблице, и я хочу рассчитать, сколько лет каждому человеку в пересчете на день, месяц и год с сегодняшнего дня, например:

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

Это запрос, который я использую в настоящее время:

 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
  

Это отобразит следующее для Joe Bloggs из приведенного выше SQL-запроса:

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

По сути, происходит то, что годы не вычитаются из месяцев и т. Д. Так что это общее количество дней, общее количество месяцев и общее количество лет, а не их разбивка, как показано выше.

Как я могу изменить приведенный выше запрос, чтобы заставить это работать, используя таблицу данных с именем Family , с полями Name и DOB ?

Я ПОПРОБОВАЛ некоторые граничные условия для этого! Пока все в порядке, попробуйте, если хотите.

 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
  

У меня есть at 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
  

ВОЗВРАТ

 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
  

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

Попробуйте приведенное ниже решение, я его не тестировал, но оно может работать :

 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]
  

Как насчет

 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 
  

Но я предлагаю вам добавить вычисляемый столбец в таблицу:

 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
  

Если вам также нужны дополнительные месяцы и дни, добавьте столбцы и для них:

 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