Ed Harper的解决方案是我发现的最简单的，当两个日期的月和日相隔1天或更少时，它永远不会返回错误的答案。我做了一个小小的修改来处理负年龄。

DECLARE @D1 AS DATETIME, @D2 AS DATETIME
SET @D2 = '2012-03-01 10:00:02'
SET @D1 = '2013-03-01 10:00:01'
SELECT
   DATEDIFF(YEAR, @D1,@D2)
   +
   CASE
      WHEN @D1<@D2 AND DATEADD(YEAR, DATEDIFF(YEAR,@D1, @D2), @D1) > @D2
      THEN - 1
      WHEN @D1>@D2 AND DATEADD(YEAR, DATEDIFF(YEAR,@D1, @D2), @D1) < @D2
      THEN 1
      ELSE 0
   END AS AGE

我得把这个扔出去。如果您使用112样式(yyyymmdd)将日期转换为一个数字，您可以使用这样的计算…

(yyyyMMdd - yyyyMMdd) / 10000 =全年差值

declare @as_of datetime, @bday datetime;
select @as_of = '2009/10/15', @bday = '1980/4/20'

select 
    Convert(Char(8),@as_of,112),
    Convert(Char(8),@bday,112),
    0 + Convert(Char(8),@as_of,112) - Convert(Char(8),@bday,112), 
    (0 + Convert(Char(8),@as_of,112) - Convert(Char(8),@bday,112)) / 10000

输出

20091015    19800420    290595  29

闰年/日和以下方法有问题，请参阅下面的更新:

try this: DECLARE @dob datetime SET @dob='1992-01-09 00:00:00' SELECT DATEDIFF(hour,@dob,GETDATE())/8766.0 AS AgeYearsDecimal ,CONVERT(int,ROUND(DATEDIFF(hour,@dob,GETDATE())/8766.0,0)) AS AgeYearsIntRound ,DATEDIFF(hour,@dob,GETDATE())/8766 AS AgeYearsIntTrunc OUTPUT: AgeYearsDecimal AgeYearsIntRound AgeYearsIntTrunc --------------------------------------- ---------------- ---------------- 17.767054 18 17 (1 row(s) affected)

以下是一些更准确的方法:

多年来最好的方法

DECLARE @Now  datetime, @Dob datetime
SELECT   @Now='1990-05-05', @Dob='1980-05-05'  --results in 10
--SELECT @Now='1990-05-04', @Dob='1980-05-05'  --results in  9
--SELECT @Now='1989-05-06', @Dob='1980-05-05'  --results in  9
--SELECT @Now='1990-05-06', @Dob='1980-05-05'  --results in 10
--SELECT @Now='1990-12-06', @Dob='1980-05-05'  --results in 10
--SELECT @Now='1991-05-04', @Dob='1980-05-05'  --results in 10

SELECT
    (CONVERT(int,CONVERT(char(8),@Now,112))-CONVERT(char(8),@Dob,112))/10000 AS AgeIntYears

您可以将上面的10000更改为10000.0并获得小数，但它不会像下面的方法那样准确。

用十进制表示年份的最佳方法

DECLARE @Now  datetime, @Dob datetime
SELECT   @Now='1990-05-05', @Dob='1980-05-05' --results in 10.000000000000
--SELECT @Now='1990-05-04', @Dob='1980-05-05' --results in  9.997260273973
--SELECT @Now='1989-05-06', @Dob='1980-05-05' --results in  9.002739726027
--SELECT @Now='1990-05-06', @Dob='1980-05-05' --results in 10.002739726027
--SELECT @Now='1990-12-06', @Dob='1980-05-05' --results in 10.589041095890
--SELECT @Now='1991-05-04', @Dob='1980-05-05' --results in 10.997260273973

SELECT 1.0* DateDiff(yy,@Dob,@Now) 
    +CASE 
         WHEN @Now >= DATEFROMPARTS(DATEPART(yyyy,@Now),DATEPART(m,@Dob),DATEPART(d,@Dob)) THEN  --birthday has happened for the @now year, so add some portion onto the year difference
           (  1.0   --force automatic conversions from int to decimal
              * DATEDIFF(day,DATEFROMPARTS(DATEPART(yyyy,@Now),DATEPART(m,@Dob),DATEPART(d,@Dob)),@Now) --number of days difference between the @Now year birthday and the @Now day
              / DATEDIFF(day,DATEFROMPARTS(DATEPART(yyyy,@Now),1,1),DATEFROMPARTS(DATEPART(yyyy,@Now)+1,1,1)) --number of days in the @Now year
           )
         ELSE  --birthday has not been reached for the last year, so remove some portion of the year difference
           -1 --remove this fractional difference onto the age
           * (  -1.0   --force automatic conversions from int to decimal
                * DATEDIFF(day,DATEFROMPARTS(DATEPART(yyyy,@Now),DATEPART(m,@Dob),DATEPART(d,@Dob)),@Now) --number of days difference between the @Now year birthday and the @Now day
                / DATEDIFF(day,DATEFROMPARTS(DATEPART(yyyy,@Now),1,1),DATEFROMPARTS(DATEPART(yyyy,@Now)+1,1,1)) --number of days in the @Now year
             )
     END AS AgeYearsDecimal

你应该使用 select FLOOR(DATEDIFF(CURDATE()，DATE(DOB))/365.25) from table_name; 这里CURDATE()使用当前日期，您可以以'yyyy-mm-dd'格式给出自己的日期 DATE(DOB)从DATETIME格式的列中提取yyyy-mm-dd年 这里DOB是你的列名(但你应该改变表修改数据类型为DATETIME在你的情况下是nvarchar) 注意: 此查询用于mysql 这个年龄在全年

下面的脚本检查现在和给定出生日期之间的年差;第二部分检查该生日在当年是否已经过去;如果不是，则减去:

SELECT year(NOW()) - year(date_of_birth) - (CONCAT(year(NOW()), '-', month(date_of_birth), '-', day(date_of_birth)) > NOW()) AS Age
FROM tableName;

这将正确地处理生日和舍入的问题:

DECLARE @dob  datetime
SET @dob='1992-01-09 00:00:00'

SELECT DATEDIFF(YEAR, '0:0', getdate()-@dob)