我有一个表，上面列出了人们的出生日期(目前是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
当前回答
我已经在我们的生产代码中使用这个查询近10年了:
SELECT FLOOR((CAST (GetDate() AS INTEGER) - CAST(Date_of_birth AS INTEGER)) / 365.25) AS Age
其他回答
试试这个
DECLARE @date datetime, @tmpdate datetime, @years int, @months int, @days int
SELECT @date = '08/16/84'
SELECT @tmpdate = @date
SELECT @years = DATEDIFF(yy, @tmpdate, GETDATE()) - CASE WHEN (MONTH(@date) > MONTH(GETDATE())) OR (MONTH(@date) = MONTH(GETDATE()) AND DAY(@date) > DAY(GETDATE())) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)
SELECT @months = DATEDIFF(m, @tmpdate, GETDATE()) - CASE WHEN DAY(@date) > DAY(GETDATE()) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(m, @months, @tmpdate)
SELECT @days = DATEDIFF(d, @tmpdate, GETDATE())
SELECT Convert(Varchar(Max),@years)+' Years '+ Convert(Varchar(max),@months) + ' Months '+Convert(Varchar(Max), @days)+'days'
这将正确地处理生日和舍入的问题:
DECLARE @dob datetime
SET @dob='1992-01-09 00:00:00'
SELECT DATEDIFF(YEAR, '0:0', getdate()-@dob)
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 ID,
Name,
DATEDIFF(yy,CONVERT(DATETIME, DOB),GETDATE()) AS AGE,
DOB
FROM MyTable
DECLARE @yourBirthDate DATETIME = '1987-05-25'
SELECT YEAR(DATEADD(DAY, DATEDIFF(DAY, @yourBirthDate, GETDATE()), CAST('0001-01-01' AS DATETIME2))) - 1
