![d913dae543c6d86b03af6a43e3874d54.png](https://i-blog.csdnimg.cn/blog_migrate/1c8e98d5d372d4a3d88333dde3218aa6.jpeg)
提前说一下,有一个bug,就是出生日期等于getdate()的时候,由于没有精确到时分秒,而无法计算年龄。
过几天补上。
/*计算明细年龄问题*/
DECLARE @ADATE DATETIME, @BIRTHDAY DATETIME
, @AYEAR INT,@AMONTH INT,@ADAY INT,@BDAY INT, @RVALUE VARCHAR(20)
SET @RVALUE = ''
SET @BIRTHDAY ='2019-06-21 10:00:00.000'
SET @ADATE = GETDATE()
SET @ADAY = DATEDIFF(DAY, @BIRTHDAY, @ADATE) --当前日期与生日的日期
IF @ADAY > 0 --即 出生日期大于当前日期,生日day>当前day
BEGIN
--当前日期与生日的月份差值
SELECT @AMONTH = DATEDIFF(MONTH, @BIRTHDAY, @ADATE),
--DATEPART 用于返回日期的单独部分,当前日期的日减去生日的日,赋值给@BDAY
@BDAY = DATEPART(DAY, @ADATE) - DATEPART(DAY, @BIRTHDAY)
SET @AYEAR = @AMONTH / 12 --年=总月份/12
SET @AMONTH = @AMONTH % 12 --总月份除以12取余
--如果当前日减去生日的日小于0 ,将总月份除以12的余数减一
IF @BDAY < 0 SET @AMONTH = @AMONTH - 1
--如果上一个条件成立,且当@AMONTH=0 时,@AMONTH = @AMONTH - 1<0 ,条件成立,年龄=@AYEAR-1,月份=@AMONTH=12+@AMONTH
IF @AMONTH < 0 SELECT @AYEAR = @AYEAR - 1, @AMONTH = 12 + @AMONTH
IF @BDAY < 0
BEGIN
SET @BIRTHDAY = DATEADD(MONTH, @AMONTH, @BIRTHDAY)
SET @BIRTHDAY = DATEADD(YEAR, @AYEAR, @BIRTHDAY)
SET @BDAY = DATEDIFF(DAY, @BIRTHDAY, @ADATE)
END
SET @RVALUE=CAST(@AYEAR AS VARCHAR)+'岁'+CAST(@AMONTH AS VARCHAR)+'月'+CAST(@BDAY AS VARCHAR)+'日'
SELECT @RVALUE
END
![97e85ee01c122636519cd83a6e8b73eb.png](https://i-blog.csdnimg.cn/blog_migrate/200658ba847d25c9f0564cc122c2c345.jpeg)
在后边加上这句,即可实现精确到分
else
if @aDay = 0
begin
set @amin = datediff(minute, @BIRTHDAY, @aDate)
if @amin > 0
begin
set @ahour = @amin / 60
set @amin =@amin % 60
if @ahour = 0 set @rvalue = convert(varchar(9), @amin) + '分'
else
if @ahour > 0 set @rvalue = convert(varchar(9), @ahour) + '时'+convert(varchar(9), @amin) + '分'
end
SELECT @RVALUE
end
![fe5b6c3815693e51e246c5dba8d7ca1d.png](https://i-blog.csdnimg.cn/blog_migrate/d87e6ff1deea90cba4cd628591c86abc.jpeg)