你可以用 datediff() 和聚合。假设您的日期列 dt 在表中 mytable ，如果你想要整张table上的平均年龄（以年为单位），那么你会做：

select avg(datediff(year, dt, getdate())) avg_age
from mytable

您可以将第一个参数改为 datediff() （这被称为日期部分），任何其他支持的价值取决于你的实际意思年龄；例如 datediff(day, dt, getdate()) 给你几天的差别。

首先，让我们正确地计算年龄。请参阅代码中的注解，并理解datediff不计算年龄。它只计算它跨越的时间边界的数量。

--===== Local obviously named variables defined and assigned
DECLARE  @StartDT DATETIME = '2019-12-31 23:59:59.997'
        ,@EndDT   DATETIME = '2020-01-01 00:00:00.000'
;
--===== Show the difference in milliseconds between the two date/times
     -- Because of the rounding that DATETIME does on 3.3ms resolution, this will return 4ms,
     -- which certainly does NOT depict an age of 1 year.
 SELECT DATEDIFF(ms,@StartDT,@EndDT)
;
--===== This solution will mistakenly return an age of 1 year for the dates given,
     -- which are only about 4ms apart according the SELECT above.
 SELECT IncorrectAgeInYears = DATEDIFF(YEAR, @StartDT, @EndDT)
;
--===== This calulates the age in years correctly in T-SQL.
     -- If the anniversary data has not yet occurred, 1 year is substracted.
 SELECT CorrectAgeInYears = DATEDIFF(yy, @StartDT, @EndDT) 
                          - IIF(DATEADD(yy, DATEDIFF(yy, @StartDT, @EndDT), @StartDT) > @EndDT, 1, 0)
;

现在，让我们将正确的计算转换为表值函数，该函数返回单个标量值，从而生成真正高速的“内联标量函数”。

CREATE FUNCTION [dbo].[AgeInYears]
        (
        @StartDT DATETIME, --Date of birth or date of manufacture or start date.
        @EndDT   DATETIME  --Usually, GETDATE() or CURRENT_TIMESTAMP but
                           --can be any date source like a column that has an end date.
        )
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
 SELECT AgeInYears = DATEDIFF(yy, @StartDT, @EndDT) 
                   - IIF(DATEADD(yy, DATEDIFF(yy, @StartDT, @EndDT), @StartDT) > @EndDT, 1, 0)
;

然后，让我们创建一个测试表并填充它。对于这个问题，这个方法有点过头了，但是对于很多不同的例子也很有用。别让这百万行吓到你。。。这在我的笔记本电脑上运行仅需2秒多，包括创建聚集索引。

--===== Create and populate a large test table on-the-fly.
     -- "SomeInt" has a range of 1 to 50,000 numbers
     -- "SomeLetters2" has a range of "AA" to "ZZ" 
     -- "SomeDecimal has a range of 10.00 to 100.00 numbers
     -- "SomeDate" has a range of >=01/01/2000 & <01/01/2020 whole dates
     -- "SomeDateTime" has a range of >=01/01/2000 & <01/01/2020 Date/Times
     -- "SomeRand" contains the value of RAND just to show it can be done without a loop.
     -- "SomeHex9" contains 9 hex digits from NEWID()
     -- "SomeFluff" is a fixed width CHAR column just to give the table a little bulk.
 SELECT TOP 1000000
         SomeInt        = ABS(CHECKSUM(NEWID())%50000) + 1
        ,SomeLetters2   = CHAR(ABS(CHECKSUM(NEWID())%26) + 65)
                        + CHAR(ABS(CHECKSUM(NEWID())%26) + 65)
        ,SomeDecimal    = CAST(RAND(CHECKSUM(NEWID())) * 90 + 10 AS DECIMAL(9,2))
        ,SomeDate       = DATEADD(dd, ABS(CHECKSUM(NEWID())%DATEDIFF(dd,'2000','2020')), '2000')
        ,SomeDateTime   = DATEADD(dd, DATEDIFF(dd,0,'2000'), RAND(CHECKSUM(NEWID())) * DATEDIFF(dd,'2000','2020'))
        ,SomeRand       = RAND(CHECKSUM(NEWID()))  --CHECKSUM produces an INT and is MUCH faster than conversion to VARBINARY.
        ,SomeHex9       = RIGHT(NEWID(),9)
        ,SomeFluff      = CONVERT(CHAR(170),'170 CHARACTERS RESERVED') --Just to add a little bulk to the table.
   INTO dbo.JBMTest
   FROM      sys.all_columns ac1 --Cross Join forms up to a 16 million rows
  CROSS JOIN sys.all_columns ac2 --Pseudo Cursor
;
GO
--===== Add a non-unique Clustered Index to SomeDateTime for this demo.
 CREATE CLUSTERED INDEX IXC_Test ON dbo.JBMTest (SomeDateTime ASC)
;

现在，让我们找出somedatetime列所代表的百万人的平均年龄。

SELECT  AvgAgeInYears = AVG(age.AgeInYears )
        ,RowsCounted   = COUNT(*)
   FROM dbo.JBMTest tst
  CROSS APPLY dbo.AgeInYears(SomeDateTime,GETDATE()) age
;

结果：