CREATE FUNCTION [dbo].[FullAge] (@birthday datetime,@calDay datetime)
RETURNS int AS
BEGIN /*
**给出出生日期,计算日期,返回周岁
**1、如果计算日期和出生日期的月份相同,就要判断计算日期是否过了出生日期
**2、如果是2月29日出生的,那如果是闰年,就是3月1号算增加1岁,如果不是闰年,3月2日增加1岁
**
*/--计算日期比出生日期小,返回NULL
if datediff(dd,@birthday,@calDay)<0
begin
return null
end--计算日期 = 出生日期,返回0
if datediff(dd,@birthday,@calDay)=0
begin
return 0
end
declare @age int
set @age = datediff(yy,@birthday,@calDay) - 1----如果月份相同,计算日期>出生日期,加1
if (datepart(mm,@birthday) = datepart(mm,@calDay)) and (datepart(dd,@birthday) < datepart(dd,@calDay))
begin
set @age = @age +1
end----如果计算月份比出生月份大,加1
if (datepart(mm,@birthday) < datepart(mm,@calDay))
begin
set @age = @age +1
end----如果出生日期是2月29日,而计算日期是3月1日,那么计算日期是闰年,要减1
if (datepart(mm,@birthday) = 2 and datepart(dd,@birthday) = 29 and datepart(mm,@calDay) = 3 and datepart(dd,@calDay) = 1
and ((datepart(yy,@calDay)%4=0 and datepart(yy,@calDay)%100!=0 )or datepart(yy,@calDay)%400=0))
begin
set @age = @age - 1
end
return @age end ---------测试:
select dbo.FullAge('2000-2-29','2005-3-1')
RETURNS int AS
BEGIN /*
**给出出生日期,计算日期,返回周岁
**1、如果计算日期和出生日期的月份相同,就要判断计算日期是否过了出生日期
**2、如果是2月29日出生的,那如果是闰年,就是3月1号算增加1岁,如果不是闰年,3月2日增加1岁
**
*/--计算日期比出生日期小,返回NULL
if datediff(dd,@birthday,@calDay)<0
begin
return null
end--计算日期 = 出生日期,返回0
if datediff(dd,@birthday,@calDay)=0
begin
return 0
end
declare @age int
set @age = datediff(yy,@birthday,@calDay) - 1----如果月份相同,计算日期>出生日期,加1
if (datepart(mm,@birthday) = datepart(mm,@calDay)) and (datepart(dd,@birthday) < datepart(dd,@calDay))
begin
set @age = @age +1
end----如果计算月份比出生月份大,加1
if (datepart(mm,@birthday) < datepart(mm,@calDay))
begin
set @age = @age +1
end----如果出生日期是2月29日,而计算日期是3月1日,那么计算日期是闰年,要减1
if (datepart(mm,@birthday) = 2 and datepart(dd,@birthday) = 29 and datepart(mm,@calDay) = 3 and datepart(dd,@calDay) = 1
and ((datepart(yy,@calDay)%4=0 and datepart(yy,@calDay)%100!=0 )or datepart(yy,@calDay)%400=0))
begin
set @age = @age - 1
end
return @age end ---------测试:
select dbo.FullAge('2000-2-29','2005-3-1')
CREATE FUNCTION [dbo].[FullAge] (@birthday datetime,@calDay datetime)
returns int
as
begin
declare @age int
if substring(convert(varchar(10),@birthday,112),5,4)='0229' and year(@calday)%4<>0
begin
set @calday=dateadd(dd,-1,@calday)
end
select @age= case when @birthday>@calday then null
when @birthday=@calday then 0
else case when substring(convert(varchar(10),@birthday,112),5,4)<= substring(convert(varchar(10),@calday,112),5,4) then datediff(yy,@birthday,@calday)
else datediff(yy,@birthday,@calday)-1 end
end
return @age
endgo
select dbo.FullAge('2000-2-29','2005-3-1')--drop function FullAge
----
4select dbo.FullAge('2000-2-29','2005-3-2')----
5
RETURNS int AS
BEGIN
if datediff(dd,@birthday,@calDay)<0
begin
return null
end--计算日期 = 出生日期,返回0
if datediff(dd,@birthday,@calDay)=0
begin
return 0
end
declare @age int
select @age=case when datediff(dd,dateadd(YY,datediff(YY,@birthday,@calDay),@birthday),@calDay)< 0 then datediff(YY,@birthday,@calDay)-1
else datediff(YY,@birthday,@calDay) end
return @age
endselect dbo.FullAge('2000-2-29','2005-3-1')
RETURNS int AS
BEGINdeclare @age int
set @age=datediff(year,@birthday,@calDay)if datediff(day,dateadd(year,@age,@birthday),@calDay)<=0
set @age=@age-1if @age<0
set @age=0return @age
end
go
select datediff(year,5,'2000-2-29')
结果是:
2005-2-28
--按你的改下
alter FUNCTION [dbo].[FullAge] (@birthday datetime,@calDay datetime)
RETURNS int AS
BEGINdeclare @age int
set @age=datediff(year,@birthday,@calDay)if datediff(day,dateadd(year,@age,@birthday),@calDay)<=0
set @age=@age-1if datepart(month,@birthday) = 2 and datepart(day,@birthday) = 29 and datepart(month,@calDay) = 3 and datepart(day,@calDay) = 1
set @age=@age-1if @age<0
set @age=0return @age
end
go
我刚刚看了这个网站,这应该从法律角度上来计算,其实公司有公司自己的制度。我只是想尽量正确点,我不搞法律,也不搞人事,所以也不确定的。
还有,不是所有的2月29日出生的,都是3月2日加1岁,而是看当年是闰年还是平年,闰年是3月1日加1岁,平年是3月2日加1岁,这是我的理解,不对之处请指正
---------------------------------------------------
平年哪里来的 2月29日我的不符合要求,没有返回null,但改回来和原来差不了多少,和前面几位的也没有什么改变alter FUNCTION [dbo].[FullAge] (@birthday datetime,@calDay datetime)
RETURNS int AS
BEGINif datediff(dd,@birthday,@calDay)<0
begin
return null
enddeclare @age int
set @age=datediff(year,@birthday,@calDay)if datediff(day,dateadd(year,@age,@birthday),@calDay)<=0
set @age=@age-1if datepart(month,@birthday) = 2 and datepart(day,@birthday) = 29 and datepart(month,@calDay) = 3 and datepart(day,@calDay) = 1
set @age=@age-1if @age<0
set @age=0return @age
end
go
RETURNS int AS
BEGINdeclare @age int
set @age=datediff(year,@birthday,@calDay)if datediff(day,dateadd(year,@age,@birthday),@calDay)<=0
set @age=@age-1if datepart(month,@birthday) = 2 and datepart(day,@birthday) = 29 and datepart(month,@calDay) = 3 and datepart(day,@calDay) = 1
and not (year(@calDay)%4=0 and (year(@calDay)%100!=0 or year(@calDay)%400=0))
set @age=@age-1if @age<0
set @age=0return @age
end
goselect dbo.FullAge('2000-2-29','2004-2-29')select dbo.FullAge('2000-2-29','2004-3-1')select dbo.FullAge('2000-2-29','2005-3-1')select dbo.FullAge('2000-2-29','2005-3-2')select dbo.FullAge('2000-2-29','2100-3-1')select dbo.FullAge('2000-2-29','2100-3-2')