使用 SQL Server 从系统日期和生日计算年龄
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13161401/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
calculating age from sysdate and birthdate using SQL Server
提问by jenswirf
Possible Duplicate:
How to calculate age in T-SQL with years, months, and days
可能的重复：
如何用年、月和日计算 T-SQL 中的年龄
I'm simply trying to calc age (in years) using a birthdate variable
我只是想使用生日变量来计算年龄（以年为单位）
1932-04-29 00:00:00.000
and SYSDATETIME( ) in SQL Server, where by
和 SYSDATETIME() 在 SQL Server 中，其中由
SELECT year(Sysdatetime() - Birthdate) as Age
produces (surprisingly) : 1980
产生（令人惊讶的）：1980
What did I miss? I expected to get 80!
我错过了什么？我预计会得到80！
回答by podiluska
Calculating age is not as simple as it might first appear.
计算年龄并不像乍看起来那么简单。
If you use Datediffyou get a difference in absolute years, which is not the age.
如果您使用，Datediff您会得到绝对年份的差异，而不是年龄。
eg
例如
select DATEDIFF(yy, '1980-12-31', getdate())
will return 32, whereas the age of the person in question is 31.
将返回 32，而相关人员的年龄是 31。
This might be accurate enough for your purposes. More accurate, but still wrong, you can use
这对于您的目的来说可能足够准确。更准确，但仍然错误，您可以使用
select convert(int,DATEDIFF(d, '1933-10-31', getdate())/365.25)
which is right mostof the time.
大多数时候这是正确的。
Or you can write a more complex function....
或者你可以写一个更复杂的函数......
回答by bummi
CREATE Function [dbo].[F_GetAge]( @RefDate Datetime,@Birthdate Datetime) Returns Int as
/*
200040916 Thomas Wassermann
*/
Begin
Declare @Alter Int
if @RefDate>@Birthdate
Select @Alter=(DatePart(yy,CAst(@Refdate-@Birthdate -1 as Float))-1900)
else select @Alter=0
Return(@Alter)
end
回答by Steven
Quick and dirty. Can have some errors bepending on leap years, etc.
又快又脏。在闰年等可能会有一些错误。
-- quick and dirty age in years
declare @birthday datetime
set @birthday = '19320429'
-- this is pretty close, but could have problems with leap years, etc.
select floor(datediff(day, @birthday, CURRENT_TIMESTAMP) / 365.25)
回答by Sebastian Meine
What happens here is the following:
这里发生的事情如下：
- both dates get converted to floats as number of days since 1900-01-01T00:00:00
- the floats get subtracted from each other yielding the number of days between them
- the result gets converted back to a date by adding the number of days to the base date
- 两个日期都转换为浮点数，作为自 1900-01-01T00:00:00 以来的天数
- 浮点数相互减去，得出它们之间的天数
- 通过将天数添加到基准日期，结果将转换回日期
To correctly calculate the difference use DATEDIFF: http://msdn.microsoft.com/en-us/library/ms189794.aspx
要正确计算差异，请使用 DATEDIFF：http: //msdn.microsoft.com/en-us/library/ms189794.aspx
But there is still a problem with that. See here for a complete solution: How to calculate age (in years) based on Date of Birth and getDate()
但这仍然存在问题。请参阅此处获取完整的解决方案：如何根据出生日期和 getDate() 计算年龄（以年为单位）
