尝试这样做:
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
输出:
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'
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'
SELECT 1.0* DateDiff(yy,@Dob,@Now)
+CASE
WHEN @Now >= DATEFROMPARTS(DATEPART(yyyy,@Now),DATEPART(m,@Dob),DATEPART(d,@Dob)) THEN
( 1.0
* DATEDIFF(day,DATEFROMPARTS(DATEPART(yyyy,@Now),DATEPART(m,@Dob),DATEPART(d,@Dob)),@Now)
/ DATEDIFF(day,DATEFROMPARTS(DATEPART(yyyy,@Now),1,1),DATEFROMPARTS(DATEPART(yyyy,@Now)+1,1,1))
)
ELSE
-1
* ( -1.0
* DATEDIFF(day,DATEFROMPARTS(DATEPART(yyyy,@Now),DATEPART(m,@Dob),DATEPART(d,@Dob)),@Now)
/ DATEDIFF(day,DATEFROMPARTS(DATEPART(yyyy,@Now),1,1),DATEFROMPARTS(DATEPART(yyyy,@Now)+1,1,1))
)
END AS AgeYearsDecimal