SQL 如何计算两个日期之间的年龄/年数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14071919/
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
How to calculate Age/Number of Years between two dates
提问by ccStars
Possible Duplicate:
How to calculate age in T-SQL with years, months, and days
可能的重复：
如何用年、月和日计算 T-SQL 中的年龄
On a project I was working on, I was required to calculate a persons age when they join the system; after looking on the internet I found various ways this could be done, but most of them had slight issues when it involved a Leap-Year.
在我正在做的一个项目中，我被要求计算一个人加入系统时的年龄；在互联网上查看后，我发现了多种方法可以做到这一点，但是当涉及闰年时，大多数方法都有一些小问题。
The solution below is how I calculate number of years past / age. Hope this helps others
下面的解决方案是我如何计算过去/年龄的年数。希望这对其他人有帮助
回答by ccStars
You need to add the following method to your database:
您需要将以下方法添加到您的数据库中：
CREATE FUNCTION [dbo].[fnCalAge] (@DiffFrom DATE, @DiffTo DATE) RETURNS INT
AS
BEGIN
DECLARE @NumOfYears INT
SET @NumOfYears = (SELECT
DATEDIFF(YEAR, @DiffFrom, @DiffTo) +
CASE
WHEN MONTH(@DiffTo) < MONTH(@DiffFrom) THEN -1
WHEN MONTH(@DiffTo) > MONTH(@DiffFrom) THEN 0
ELSE
CASE WHEN DAY(@DiffTo) < DAY(@DiffFrom) THEN -1 ELSE 0 END
END)
IF @NumOfYears < 0
BEGIN
SET @NumOfYears = 0;
END
RETURN @NumOfYears;
END
You then call it in your SQL Query, similar to the following:
然后在 SQL 查询中调用它，类似于以下内容：
SET DATEFORMAT dmy
SELECT dbo.fnCalAge(CAST('20/06/1987' AS DATE), CAST('20/06/2013' AS DATE))
回答by Charles Bretana
assuming @bDate is datetime of birthdate and @today is todays date, then...
假设@bDate 是生日的日期时间，@today 是今天的日期，那么...
Declare @bDay Date = '31 dec 2000'
Declare @today Date = cast(getdate() as date)
Select datediff(Year, @bDay, @today) -
case When datepart(dayofYear, @today) <
datepart(dayofYear, @bDay) Then 1 Else 0 End
回答by Art
Replace hiredate with DOB for age. Replace sysdate with your date such as to_date('28-DEC-2012') :
用 DOB 替换hiredate for age。将 sysdate 替换为您的日期，例如 to_date('28-DEC-2012') ：
SELECT empno, ename, hiredate, TRUNC(MONTHS_BETWEEN(sysdate, hiredate)/12) years_of_service
FROM scott.emp
/
