我有一个表，上面列出了人们的出生日期(目前是nvarchar(25))
我如何将其转换为日期，然后以年为单位计算他们的年龄?
我的数据如下所示
ID Name DOB
1 John 1992-01-09 00:00:00
2 Sally 1959-05-20 00:00:00
我希望看到:
ID Name AGE DOB
1 John 17 1992-01-09 00:00:00
2 Sally 50 1959-05-20 00:00:00
当前回答
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
其他回答
select floor((datediff(day,0,@today) - datediff(day,0,@birthdate)) / 365.2425) as age
这里有很多365.25的答案。记住闰年是如何定义的:
每四年一次 除了每100年 除了每400年
在尝试了许多方法后，这是100%的工作时间使用现代MS SQL格式函数，而不是转换为风格112。两者都可以，但这是最少的代码。
谁能找到一个日期组合不工作?我不认为有一个:)
--Set parameters, or choose from table.column instead:
DECLARE @DOB DATE = '2000/02/29' -- If @DOB is a leap day...
,@ToDate DATE = '2018/03/01' --...there birthday in this calculation will be
--0+ part tells SQL to calc the char(8) as numbers:
SELECT [Age] = (0+ FORMAT(@ToDate,'yyyyMMdd') - FORMAT(@DOB,'yyyyMMdd') ) /10000
是什么:
DECLARE @DOB datetime
SET @DOB='19851125'
SELECT Datepart(yy,convert(date,GETDATE())-@DOB)-1900
这难道不会避免所有的舍入、截断和抵消问题吗?
SELECT ID,
Name,
DATEDIFF(yy,CONVERT(DATETIME, DOB),GETDATE()) AS AGE,
DOB
FROM MyTable
闰年/日和以下方法有问题，请参阅下面的更新:
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
