/*
6、编写一个函数func_calcdays,计算下一个生日还有多少天,函数
带两个输入参数(生日和当前日期),一个返回值(天数)。
*/
go
if object_id('func_calcdays')is not null
drop function func_calcdays
go
create function func_calcdays(
@birthday as datetime , @currentdate as datetime )
returns int
as
begin
declare @currentage int,@nextbirthday datetime,@days int
if month(@currentdate)>month(@birthday)
--当前月份大于生日所在月份,表示生日已经过了
begin
select @currentage=datediff(yy,@birthday,@currentdate)
select @nextbirthday=dateadd(yy,(@currentage+1),@birthday)
select @days=datediff(dd,@currentdate,@nextbirthday)
end
else
if month(@currentdate)<month(@birthday)
--当前月份小于生日所在月份,生日还没过,生一个生日是去年
begin
select @currentage=datediff(yy,@birthday,@currentdate)
select @nextbirthday=dateadd(yy,@currentage,@birthday)
select @days=datediff(dd,@currentdate,@nextbirthday)
end
else
--当前月份等于生日所在月份
if day(@birthday)>day(@currentdate)--生日日期大于当前日期,生日还没过,且就在当前这一个月
begin
select @days=day(@birthday)-day(@currentdate)
end
else--生日日期小于当前日期,生日已经过了,且就在当前这一个月
begin
select @currentage=datediff(yy,@birthday,@currentdate)
select @nextbirthday=dateadd(yy,(@currentage+1),@birthday)
select @days=datediff(dd,@currentdate,@nextbirthday)
end
return @days
end--调用函数
select dbo . func_calcdays ( '1990-11-23' , getdate ())/*
6、编写一个函数func_calcdays,计算下一个生日还有多少天,函数
带两个输入参数(生日和当前日期),一个返回值(天数)。
*/create function func_calcdays(@birth datetime,@curr datetime)
returns int
as
begin
declare @nextbirth datetime,@days int
if(month(@curr)>month(@birth))--生日已过
begin
select @nextbirth=dateadd(year,datediff(year,@birth,@curr)+1,@birth);
select @days= datediff(day,@curr,@nextbirth);
end
else if(month(@curr)=month(@birth))
begin
if(day(@curr)>day(@birth))--生日已过
begin
select @nextbirth=dateadd(year,datediff(year,@birth,@curr)+1,@birth);
select @days= datediff(day,@curr,@nextbirth);
end
else --生日未过
begin
select @nextbirth=dateadd(year,datediff(year,@birth,@curr),@birth);
select @days= datediff(day,@curr,@nextbirth);
end
end
else --生日未过
begin
select @nextbirth=dateadd(year,datediff(year,@birth,@curr),@birth);
select @days= datediff(day,@curr,@nextbirth);
end
return @days
endselect dbo.func_calcdays('1980-12-15',getdate())
--建立存储过程实现:
go
if object_id('pro_calcdays')is not null
drop proc pro_calcdays
go
create proc pro_calcdays @birthday datetime,@days int output
as
select @days=
case
when
month(@birthday)<month(getdate())
then
datediff(dd,getdate(),dateadd(yy,(year(getdate())-year(@birthday)+1),@birthday))
--case
when
month(@birthday)>month(getdate())
then
datediff(dd,getdate(),dateadd(yy,(year(@birthday)-year(getdate())),@birthday))
--case
when
month(@birthday)=month(getdate()) and day(@birthday)>day(getdate())
then
day(@birthday)-day(getdate())
else
datediff(dd,getdate(),dateadd(yy,(year(getdate())-year(@birthday)+1),@birthday))
enddeclare @days int
exec pro_calcdays '1990-12-19',@days output
select @days as day
6、编写一个函数func_calcdays,计算下一个生日还有多少天,函数
带两个输入参数(生日和当前日期),一个返回值(天数)。
*/
go
if object_id('func_calcdays')is not null
drop function func_calcdays
go
create function func_calcdays(
@birthday as datetime , @currentdate as datetime )
returns int
as
begin
declare @currentage int,@nextbirthday datetime,@days int
if month(@currentdate)>month(@birthday)
--当前月份大于生日所在月份,表示生日已经过了
begin
select @currentage=datediff(yy,@birthday,@currentdate)
select @nextbirthday=dateadd(yy,(@currentage+1),@birthday)
select @days=datediff(dd,@currentdate,@nextbirthday)
end
else
if month(@currentdate)<month(@birthday)
--当前月份小于生日所在月份,生日还没过,生一个生日是去年
begin
select @currentage=datediff(yy,@birthday,@currentdate)
select @nextbirthday=dateadd(yy,@currentage,@birthday)
select @days=datediff(dd,@currentdate,@nextbirthday)
end
else
--当前月份等于生日所在月份
if day(@birthday)>day(@currentdate)--生日日期大于当前日期,生日还没过,且就在当前这一个月
begin
select @days=day(@birthday)-day(@currentdate)
end
else--生日日期小于当前日期,生日已经过了,且就在当前这一个月
begin
select @currentage=datediff(yy,@birthday,@currentdate)
select @nextbirthday=dateadd(yy,(@currentage+1),@birthday)
select @days=datediff(dd,@currentdate,@nextbirthday)
end
return @days
end--调用函数
select dbo . func_calcdays ( '1990-11-23' , getdate ())/*
6、编写一个函数func_calcdays,计算下一个生日还有多少天,函数
带两个输入参数(生日和当前日期),一个返回值(天数)。
*/create function func_calcdays(@birth datetime,@curr datetime)
returns int
as
begin
declare @nextbirth datetime,@days int
if(month(@curr)>month(@birth))--生日已过
begin
select @nextbirth=dateadd(year,datediff(year,@birth,@curr)+1,@birth);
select @days= datediff(day,@curr,@nextbirth);
end
else if(month(@curr)=month(@birth))
begin
if(day(@curr)>day(@birth))--生日已过
begin
select @nextbirth=dateadd(year,datediff(year,@birth,@curr)+1,@birth);
select @days= datediff(day,@curr,@nextbirth);
end
else --生日未过
begin
select @nextbirth=dateadd(year,datediff(year,@birth,@curr),@birth);
select @days= datediff(day,@curr,@nextbirth);
end
end
else --生日未过
begin
select @nextbirth=dateadd(year,datediff(year,@birth,@curr),@birth);
select @days= datediff(day,@curr,@nextbirth);
end
return @days
endselect dbo.func_calcdays('1980-12-15',getdate())
--建立存储过程实现:
go
if object_id('pro_calcdays')is not null
drop proc pro_calcdays
go
create proc pro_calcdays @birthday datetime,@days int output
as
select @days=
case
when
month(@birthday)<month(getdate())
then
datediff(dd,getdate(),dateadd(yy,(year(getdate())-year(@birthday)+1),@birthday))
--case
when
month(@birthday)>month(getdate())
then
datediff(dd,getdate(),dateadd(yy,(year(@birthday)-year(getdate())),@birthday))
--case
when
month(@birthday)=month(getdate()) and day(@birthday)>day(getdate())
then
day(@birthday)-day(getdate())
else
datediff(dd,getdate(),dateadd(yy,(year(getdate())-year(@birthday)+1),@birthday))
enddeclare @days int
exec pro_calcdays '1990-12-19',@days output
select @days as day
goinsert INTO TT(id,xm,sr)
SELECT 1,'小李','1983-12-5'
UNION ALL
SELECT 2,'小王','1997-12-29'SELECT * ,
DATEDIFF(DD, GETDATE(),
CONVERT(VARCHAR, YEAR(GETDATE())) + '-'
+ RIGHT(CONVERT(VARCHAR(10), sr, 120), 5)) AS dd
FROM dbo.TT/*
ID XM SR dd
1 小李 1983-12-05 00:00:00.000 -13
2 小王 1997-12-29 00:00:00.000 11*/
DATEDIFF(DD, GETDATE(),
CONVERT(VARCHAR, YEAR(GETDATE())) + '-'
+ RIGHT(CONVERT(VARCHAR(10), sr, 120), 5)) AS dd
FROM dbo.TT 3楼的 简介明了