如何使用年,月和日计算T-SQL中的年龄

在T-SQL(SQL Server 2000)中计算某人年龄,月数和天数的最佳方法是什么？

该datediff功能不能很好地处理年份界限,加上将月份和日期分开将是一个熊.我知道我可以在客户端相对容易地做到这一点,但我想在我的存储过程中完成它.

1> Dane..：

---

这是一些T-SQL,它为您提供自@date中指定日期以来的年,月和日数.它考虑到DATEDIFF()在不考虑它的月份或日期的情况下计算差异的事实(因此8/31和9/1之间的月份差异为1个月)并使用case语句处理该结果,该语句将结果递减到适当.

DECLARE @date datetime, @tmpdate datetime, @years int, @months int, @days int
SELECT @date = '2/29/04'

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 @years, @months, @days

---

上面用缩短语法的注释是错误的; 需要单独检查月份更大,然后如果月份相同做日检查

2> tkerwood..：

---

试试这个...

SELECT CASE WHEN
 (DATEADD(year,DATEDIFF(year, @datestart  ,@dateend) , @datestart) > @dateend)
THEN DATEDIFF(year, @datestart  ,@dateend) -1
ELSE DATEDIFF(year, @datestart  ,@dateend)
END

基本上是"DateDiff(年......",给你这个人将在今年转的年龄,所以我只是添加一个案例陈述说,如果他们今年还没有过生日,那么减去1年,否则返回值.

---

短一些的版本`SELECT DATEDIFF（year，@datestart，@ dateend）+ CASE WHEN（DATEADD（year，DATEDIFF（year，@datestart，@ dateend），@datestart）> @dateend）然后-1 ELSE 0 END`

3> 小智..：

---

将文本作为文本获取年龄的简单方法如下:

Select cast((DATEDIFF(m, date_of_birth, GETDATE())/12) as varchar) + ' Y & ' + 
       cast((DATEDIFF(m, date_of_birth, GETDATE())%12) as varchar) + ' M' as Age

结果格式为:

**63 Y & 2 M**

4> Jaugar Chang..：

---

通过算术实现，采用ISO格式的日期。

declare @now date,@dob date, @now_i int,@dob_i int, @days_in_birth_month int
declare @years int, @months int, @days int
set @now = '2013-02-28' 
set @dob = '2012-02-29' -- Date of Birth

set @now_i = convert(varchar(8),@now,112) -- iso formatted: 20130228
set @dob_i = convert(varchar(8),@dob,112) -- iso formatted: 20120229
set @years = ( @now_i - @dob_i)/10000
-- (20130228 - 20120229)/10000 = 0 years

set @months =(1200 + (month(@now)- month(@dob))*100 + day(@now) - day(@dob))/100 %12
-- (1200 + 0228 - 0229)/100 % 12 = 11 months

set @days_in_birth_month = day(dateadd(d,-1,left(convert(varchar(8),dateadd(m,1,@dob),112),6)+'01'))
set @days = (sign(day(@now) - day(@dob))+1)/2 * (day(@now) - day(@dob))
          + (sign(day(@dob) - day(@now))+1)/2 * (@days_in_birth_month - day(@dob) + day(@now))
-- ( (-1+1)/2*(28 - 29) + (1+1)/2*(29 - 29 + 28))
-- Explain: if the days of now is bigger than the days of birth, then diff the two days
--          else add the days of now and the distance from the date of birth to the end of the birth month 
select @years,@months,@days -- 0, 11, 28 

测试用例

天数的方法与接受的答案不同，以下注释中显示了差异：

       dob        now  years  months  days 
2012-02-29 2013-02-28      0      11    28  --Days will be 30 if calculated by the approach in accepted answer. 
2012-02-29 2016-02-28      3      11    28  --Days will be 31 if calculated by the approach in accepted answer, since the day of birth will be changed to 28 from 29 after dateadd by years. 
2012-02-29 2016-03-31      4       1     2
2012-01-30 2016-02-29      4       0    30
2012-01-30 2016-03-01      4       1     2  --Days will be 1 if calculated by the approach in accepted answer, since the day of birth will be changed to 30 from 29 after dateadd by years.
2011-12-30 2016-02-29      4       1    30

Days by case语句的简短版本：

set @days = CASE WHEN day(@now) >= day(@dob) THEN day(@now) - day(@dob)
                 ELSE @days_in_birth_month - day(@dob) + day(@now) END

如果您只需要几岁和几个月的年龄，可能会更简单

set @years = ( @now_i/100 - @dob_i/100)/100
set @months =(12 + month(@now) - month(@dob))%12 
select @years,@months -- 1, 0

注意：SQL Server日期格式的一个非常有用的链接