34,576
社区成员
发帖
与我相关
我的任务
分享
declare @d datetime
set @d=convert(datetime,'2000-1-1 '+'00:02:10');
select datepart(hh,@d)*3600+datepart(mi,@d)*60+datepart(ss,@d)
DECLARE @time time(4)
set @time='00:02:10'
select DATEDIFF(s,0,@time)
字符串处理函数用的多了 自然就会了
DECLARE @time varchar(20)
SET @time = '00:02:10'
SELECT DATEDIFF(second,'',@time)
declare @str='00:02:10'
select datediff(second,convert(varchar(10),'00:00:00',108),@str)
--130
SELECT DATEDIFF(second,'00:00:00','00:02:10')
DECLARE @time varchar(10)
set @time='00:02:10'
select datediff(s,0,CAST(@time as datetime)) as dts1
select convert(int,substring(convert(varbinary(8),CAST(@time as datetime)),5,4))/300 as dts1
dts1
-----------
130
(1 行受影响)
dts1
-----------
130
(1 行受影响)
--sql server 2008
select datediff(s,'00:00:00',CONVERT(time, '00:02:10'))
DECLARE @time time(4)
set @time='00:02:10'
select DATEDIFF(s,0,@time)
-----------
130
(1 行受影响)
declare @sql varchar(10)
set @sql='00:02:10'
select [second]=cast(left(ltrim(@sql),2) as int)*3600
+cast(substring(@sql,charindex(':',@sql)+1,2) as int)*60
+cast(right(rtrim(@sql),2) as int)
/*
second
--------------------
130
*/
create table tb(col varchar(8))
insert into tb values('00:02:10')
insert into tb values('01:02:10')
insert into tb values('10:02:10')
select datediff(ss,convert(varchar(10),getdate(),120) ,convert(varchar(11),getdate(),120) + col) from tb
/*
-----------
130
3730
36130
(所影响的行数为 3 行)
*/
select datediff(ss,convert(varchar(10),getdate(),120) ,convert(varchar(11),getdate(),120) + '00:02:10')
/*
-----------
130
(所影响的行数为 1 行)
*/
declare @sql varchar(20)
set @sql='00:02:10'
select cast(substring(right(@sql,5),0,charindex(':',right(@sql,5))) as int)*60+
substring(right(@sql,5),charindex(':',right(@sql,5))+1,len(right(@sql,5))-charindex(':',right(@sql,5)))
-----------
130
(1 行受影响)