if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_split]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[f_split] GO/*--得到字符串列表指定位置的字符
可以自定义字符串列表的分隔符 如果取数位置超出的范围,返回空字符串--邹建 2004.07(引用请保留此信息)--*//*--调用示例 --测试数据 declare @t table(FITEM varchar(100)) insert @t select '100.120.10' union all select '20.140.10' union all select '150.124.150.10'
--查询 select fitem1=dbo.f_split(fitem,1,'.') ,fitem2=dbo.f_split(fitem,2,'.') ,fitem3=dbo.f_split(fitem,3,'.') ,fitem4=dbo.f_split(fitem,4,'.') from @t --*/ create function f_split( @s varchar(8000), --字符串列表 @pos int, --取数位置 @splitchar varchar(10) --分隔符 )returns varchar(8000) as begin declare @i int,@ilen int select @i=charindex(@splitchar,@s),@ilen=len(@splitchar) while @i>0 and @pos>1 select @s=substring(@s,@i+@ilen,8000) ,@i=charindex(@splitchar,@s) ,@pos=@pos-1 return(case @pos when 1 then case when @i>0 then left(@s,@i-1) else @s end else '' end) end go--测试 declare @tb table(dt varchar(10)) insert @tb select '00:01:28' union select '00:00:00' union select '00:00:24' union select '00:00:02' union select '00:00:24' union select '00:00:10' union select '00:00:48' union select '00:00:18' union select '00:00:14' union select '00:00:44' --查询 select [总秒数]=sum( convert(int,dbo.f_split(dt,1,':'))*3600 + convert(int,dbo.f_split(dt,2,':'))*60 + convert(int,dbo.f_split(dt,3,':')) ) from @tb--结果 /*总秒数 ----------- 248.00(所影响的行数为 1 行) */
--适用于总时间在99小时以下 --建立测试环境 create table t(a varchar(50)) insert t select '00:01:28' insert t select '00:00:08' insert t select '00:00:24' insert t select '00:00:02' insert t select '00:00:24' insert t select '00:00:10' insert t select '00:00:48' insert t select '00:00:18' insert t select '00:00:14' insert t select '00:00:44'--查询 declare @a int,@b int,@c int,@d int,@e int,@f int set @a=0 set @b=0 set @c=0 select @a=@a+datepart(hh,a) from t select @b=@b+datepart(mi,a) from t select @c=@c+datepart(s,a) from t set @d=@c%60 set @e=(@c/60+@b)%60 set @f=(@c/60+@b)/60 select right(convert(varchar(4),100+@f),2)+':'+right(convert(varchar(4),100+@e),2)+':'+right(convert(varchar(4),100+@d),2) --结果 -------------- 00:04:40(所影响的行数为 1 行)
楼上正解!但要改下,可能上楼打错了--查询 declare @a int,@b int,@c int,@d int,@e int,@f int set @a=0 set @b=0 set @c=0 select @a=@a+datepart(hh,a) from t select @b=@b+datepart(mi,a) from t select @c=@c+datepart(s,a) from t set @d=@c%60 set @e=(@c/60+@b)%60 set @f=(@c/60+@b)%60 ----就在这里的,用% select right(convert(varchar(4),100+@f),2)+':'+right(convert(varchar(4),100+@e),2)+':'+right(convert(varchar(4),100+@d),2)
--是有问题,不过不是那里,改一下 --查询 declare @a int,@b int,@c int,@d int,@e int,@f int set @a=0 set @b=0 set @c=0 select @a=@a+datepart(hh,a) from t select @b=@b+datepart(mi,a) from t select @c=@c+datepart(s,a) from t set @d=@c%60 set @e=(@c/60+@b)%60 set @f=@a+(@c/60+@b)/60 --改这里 select right(convert(varchar(4),100+@f),2)+':'+right(convert(varchar(4),100+@e),2)+':'+right(convert(varchar(4),100+@d),2)
hsj20041004(光芒)
给我的语句
只是将日期加1天,并非我要的结果
drop function [dbo].[f_split]
GO/*--得到字符串列表指定位置的字符
可以自定义字符串列表的分隔符
如果取数位置超出的范围,返回空字符串--邹建 2004.07(引用请保留此信息)--*//*--调用示例 --测试数据
declare @t table(FITEM varchar(100))
insert @t select '100.120.10'
union all select '20.140.10'
union all select '150.124.150.10'
--查询
select fitem1=dbo.f_split(fitem,1,'.')
,fitem2=dbo.f_split(fitem,2,'.')
,fitem3=dbo.f_split(fitem,3,'.')
,fitem4=dbo.f_split(fitem,4,'.')
from @t
--*/
create function f_split(
@s varchar(8000), --字符串列表
@pos int, --取数位置
@splitchar varchar(10) --分隔符
)returns varchar(8000)
as
begin
declare @i int,@ilen int select @i=charindex(@splitchar,@s),@ilen=len(@splitchar)
while @i>0 and @pos>1
select @s=substring(@s,@i+@ilen,8000)
,@i=charindex(@splitchar,@s)
,@pos=@pos-1
return(case @pos when 1
then case when @i>0 then left(@s,@i-1) else @s end
else '' end)
end
go--测试
declare @tb table(dt varchar(10))
insert @tb
select '00:01:28' union
select '00:00:00' union
select '00:00:24' union
select '00:00:02' union
select '00:00:24' union
select '00:00:10' union
select '00:00:48' union
select '00:00:18' union
select '00:00:14' union
select '00:00:44' --查询
select [总秒数]=sum(
convert(int,dbo.f_split(dt,1,':'))*3600
+
convert(int,dbo.f_split(dt,2,':'))*60
+
convert(int,dbo.f_split(dt,3,':'))
)
from @tb--结果
/*总秒数
-----------
248.00(所影响的行数为 1 行)
*/
--建立测试环境
create table t(a varchar(50))
insert t select '00:01:28'
insert t select '00:00:08'
insert t select '00:00:24'
insert t select '00:00:02'
insert t select '00:00:24'
insert t select '00:00:10'
insert t select '00:00:48'
insert t select '00:00:18'
insert t select '00:00:14'
insert t select '00:00:44'--查询
declare @a int,@b int,@c int,@d int,@e int,@f int
set @a=0
set @b=0
set @c=0
select @a=@a+datepart(hh,a) from t
select @b=@b+datepart(mi,a) from t
select @c=@c+datepart(s,a) from t
set @d=@c%60
set @e=(@c/60+@b)%60
set @f=(@c/60+@b)/60
select right(convert(varchar(4),100+@f),2)+':'+right(convert(varchar(4),100+@e),2)+':'+right(convert(varchar(4),100+@d),2)
--结果
--------------
00:04:40(所影响的行数为 1 行)
declare @a int,@b int,@c int,@d int,@e int,@f int
set @a=0
set @b=0
set @c=0
select @a=@a+datepart(hh,a) from t
select @b=@b+datepart(mi,a) from t
select @c=@c+datepart(s,a) from t
set @d=@c%60
set @e=(@c/60+@b)%60
set @f=(@c/60+@b)%60 ----就在这里的,用%
select right(convert(varchar(4),100+@f),2)+':'+right(convert(varchar(4),100+@e),2)+':'+right(convert(varchar(4),100+@d),2)
--是有问题,不过不是那里,改一下
--查询
declare @a int,@b int,@c int,@d int,@e int,@f int
set @a=0
set @b=0
set @c=0
select @a=@a+datepart(hh,a) from t
select @b=@b+datepart(mi,a) from t
select @c=@c+datepart(s,a) from t
set @d=@c%60
set @e=(@c/60+@b)%60
set @f=@a+(@c/60+@b)/60 --改这里
select right(convert(varchar(4),100+@f),2)+':'+right(convert(varchar(4),100+@e),2)+':'+right(convert(varchar(4),100+@d),2)