评论

收藏

[SQL Server] sql通过日期判断年龄函数的示例代码

数据库 数据库 发布于:2021-08-03 11:43 | 阅读数:453 | 评论:0

定义函数:
CREATE FUNCTION [dbo].[GetAge]  
(  
@BirthDay nvarchar(20) --生日  
)  
RETURNS varchar(20)  
AS  
BEGIN  
if(@BirthDay is NUlL or @BirthDay='')
return '';
 -- Declare the return variable here  
 DECLARE @age varchar(20)  
 DECLARE @years int  
 DECLARE @months int  
 DECLARE @days int  
 -- Add the T-SQL statements to compute the return value here  
 set @age = ''  
  
 set @years = year(GETDATE()) - year(@birthday)  
 set @months = month(GETDATE()) - month(@birthday)  
 if day(@birthday)<=day(GETDATE())  
   set @days = day(GETDATE()) - day(@birthday)  
 else  
   begin  
   set @months = @months - 1  
   if MONTH(@birthday) in (1,3,5,7,8,10,12)  
     set @days = 31-day(@birthday)+day(GETDATE())  
   else if MONTH(@birthday) in (4,6,9,11)  
     set @days = 30-day(@birthday)+day(GETDATE())  
   else if MONTH(@birthday) = 2  
     if (year(@birthday)%4 = 0 and year(@birthday)%100 <> 0) or year(@birthday)%400 = 0  
     set @days = 29-day(@birthday)+day(GETDATE())  
     else  
     set @days = 28-day(@birthday)+day(GETDATE())  
   end  
 if @months < 0  
   begin  
   set @years = @years - 1  
   set @months = @months + 12  
   end  
 if @years = 0 and @months = 0  
 begin  
   return convert(varchar,@days+1) + '天'  
  end  
 if @years > 0  
   set @age = cast(@years as varchar(5)) + '岁'  
 if @years < 3 and @months > 0 and @years>-1  
 begin  
   set @age = @age + cast(@months as varchar(5)) + '月'  
 end  
 if @years<0  
 set @age=''  
 RETURN @age  
END
使用函数:
DSC0000.png

到此这篇关于sql通过日期判断年龄函数的示例代码的文章就介绍到这了,更多相关sql日期计算年龄内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家!

关注下面的标签,发现更多相似文章