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

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

2021-08-21 21:53kueizheng Sql Server

这篇文章主要介绍了sql通过日期判断年龄函数，本文通过示例代码给大家介绍的非常详细，对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下

定义函数：

				?

									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