--别急,刚想出来--相减的函数 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
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/*--测试结果 -------------------------------------------------- 22时55分13秒(所影响的行数为 1 行) --*/
嗯!年月的确不好算!除非假定一年365天一月30天!, 不过似乎意义不大!我依样画葫芦,加了个天上去,不知道对不对! 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
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
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/*--测试结果
--------------------------------------------------
22时55分13秒(所影响的行数为 1 行)
--*/
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