if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_datediff]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_datediff]
GO
--日期相减的函数,得到两个日期相减的时分秒
create function f_datediff(
@dt1 datetime,
@dt2 datetime
)returns varchar(50)
as
begin
declare @d int,@h int,@m int,@s int
select @d = datediff(second,@dt1,@dt2)/(24*60*60),
@h=datediff(second,@dt1,@dt2)%(24*60*60)/3600
,@m=(datediff(second,@dt1,@dt2)%3600)/60
,@s=(datediff(second,@dt1,@dt2)%3600)%60
return(case @d when 0 then
case @h when 0 then
case @m when 0 then
case @s when 0 then '' else cast(@s as varchar)+'秒' end
else cast(@m as varchar)+'分'+cast(@s as varchar)+'秒' end
else cast(@h as varchar)+'时'+cast(@m as varchar)+'分'+cast(@s as varchar)+'秒' end
else cast(@d as varchar)+'天'+cast(@h as varchar)+'时'+cast(@m as varchar)+'分'+cast(@s as varchar)+'秒' end
)
end
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_datediff]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_datediff]
GO
--日期相减的函数,得到两个日期相减的时分秒
create function f_datediff(
@dt1 datetime,
@dt2 datetime
)returns varchar(50)
as
begin
declare @h int,@m int,@s int
select @h=datediff(second,@dt1,@dt2)/3600
,@m=(datediff(second,@dt1,@dt2)%3600)/60
,@s=(datediff(second,@dt1,@dt2)%3600)%60
return(case @h when 0 then
case @m when 0 then
case @s when 0 then '' else cast(@s as varchar)+'秒' end
else cast(@m as varchar)+'分'+cast(@s as varchar)+'秒' end
else cast(@h as varchar)+'时'+cast(@m as varchar)+'分'+cast(@s as varchar)+'秒' end
)
end
go
--调用测试
select dbo.f_datediff('2003-01-01 10:28:18','2003-01-02 9:23:31')
go
--相减的函数
create function f_datediff(
@dt1 datetime,
@dt2 datetime
)returns varchar(50)
as
begin
declare @h int,@m int,@s int
select @h=datediff(second,@dt1,@dt2)/3600
,@m=(datediff(second,@dt1,@dt2)%3600)/60
,@s=(datediff(second,@dt1,@dt2)%3600)%60
return(case @h when 0 then
case @m when 0 then
case @s when 0 then '' else cast(@s as varchar)+'秒' end
else cast(@m as varchar)+'分'+cast(@s as varchar)+'秒' end
else cast(@h as varchar)+'时'+cast(@m as varchar)+'分'+cast(@s as varchar)+'秒' end
)
end
go