建立一个统计时间表:timetb(时间是24小时,间隔为十分钟)
starttime endtime
00:01:01 00:10:01
00:11:01 00:20:01
..........
23:51:01 23:59:59
starttime endtime
00:01:01 00:10:01
00:11:01 00:20:01
..........
23:51:01 23:59:59
解决方案 »
- 关于索引的问题 帮帮我 我没分了
- SQL 2008用域名无法连接
- 改了SQL 的端口,用原来的连接字符串可以连接,加了端口却不能连接,不解,请高手帮助
- 求助sql server语句
- 一个菜鸟级的问题想请教大家
- 请问那里可以下载SQLServer2000
- ALTER TABLE sql MODIFY Col001 varchar(8) ;也会错?????
- 求改一条MSSQL更新语句
- 大家开始3层了吗?还是继续用存储过程?欢迎大家讨论。
- 奇怪,同一条语句在一个SQL服务器上能运行,而在另一台SQL服务器上却不能运行
- 关于查询问题求教!!!
- 用case语句拼接null值的varchar,结果也会是null,怎么办?
create table timetb (starttime nvarchar(10),endtime nvarchar(10))
insert into timetb values('00:01:00','00:09:59')
while (select max(endtime) from timetb )<'23:59:59'
insert into timetb
select replace(Convert(nvarchar(20),dateadd(mi,10,Convert(datetime,max(starttime))),120),'1900-01-01 ',''),
replace(Convert(nvarchar(20),dateadd(mi,10,Convert(datetime,max(endtime))),120),'1900-01-01 ','')
from timetb
(date_time datetime)declare @i smallint
set @i=1
while (@i<=10)
begin
waitfor delay '00:00:05'--这里改为'00:10:00'
insert into t
select getdate()
set @i=@i+1
end
date_time
-----------------------------------
2007-05-29 14:21:15.187
2007-05-29 14:21:20.187
2007-05-29 14:21:25.187
2007-05-29 14:21:30.187
2007-05-29 14:21:35.187
2007-05-29 14:21:40.187
2007-05-29 14:21:45.187
2007-05-29 14:21:50.187
2007-05-29 14:21:55.187
2007-05-29 14:22:00.187(所影响的行数为 10 行)
declare @t nvarchar(10)
set @t='00:01:01'
update timetb set starttime=@t,@t=replace(Convert(nvarchar(20),dateadd(mi,10,Convert(datetime,@t)),120),'1900-01-01 ','')
在楼上基础上,若你的表乱了,你可以再重新更新阿:嗬嗬
create table t
(starttime char(8),
endtime char(8))declare @i smallint
set @i=1
while (@i<=144)
begin
waitfor delay '00:10:00'
declare @starttime char(8)
declare @endtime char(8)
select @starttime=cast(datepart(hh,getdate()) as char(2))+':'
+cast(datepart(m,getdate()) as char(2))+':'
+cast(datepart(s,getdate()) as char(2))
select @endtime=left(@starttime,6)+(cast(cast(right(@starttime,2) as int)+1 as char(2)))
insert into t
select @starttime,@endtime
set @i=@i+1
end
select * from t
drop table t
纠正:create table t
(starttime char(8),
endtime char(8))declare @i smallint
set @i=1
while (@i<=144)
begin
waitfor delay '00:10:00'
declare @starttime char(8)
declare @endtime char(8)
select @starttime=cast(datepart(hh,getdate()) as char(2))+':'
+cast(datepart(mi,getdate()) as char(2))+':'
+cast(datepart(s,getdate()) as char(2))
select @endtime=left(@starttime,6)+(cast(cast(right(@starttime,2) as int)+1 as char(2)))
insert into t
select @starttime,@endtime
set @i=@i+1
end
select * from t
drop table t
set @i=1
while (@i<=10)
begin
waitfor delay '00:00:05'--这里改为'00:10:00'
insert into t
select getdate()
set @i=@i+1
end放在JOB 中执行,@i=24*60/10