如何插入一列时间

yilaozhuang 2010-05-19 10:20:29
我想在表里写入两列时间，每隔5分钟插入一个起始和结束时间，该怎么写：
下面是我写的基本语句：
declare @i int

set @i=1

while @i<12*24

begin

insert into 表 (起始时间，结束时间) values （5分钟一个数）//这里每半个小时一个数据该怎么写？比如1：00（开始实际），1：05（结束时间）

set @i=@i+1

end
...全文
186 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
黄_瓜 2010-05-19
  • 打赏
  • 举报
 回复
[Quote=引用 8 楼 yilaozhuang 的回复:]
引用 2 楼 ldslove 的回复:
SQL code

create table #test1(begindate datetime,enddate datetime)


declare @i int,@begin datetime,@hours int
set @begin='2010-05-19 1:00:00'
set @hours=24
set @i=1
whil……
[/Quote]

INSERT INTO RT_JD(Time_start, Time_end)

SELECT     @begin, dateadd(mi, 5, @begin)//这里提示说有错误，我不是太懂错哪里了
yilaozhuang 2010-05-19
  • 打赏
  • 举报
 回复
[Quote=引用 2 楼 ldslove 的回复:]
SQL code

create table #test1(begindate datetime,enddate datetime)


declare @i int,@begin datetime,@hours int
set @begin='2010-05-19 1:00:00'
set @hours=24
set @i=1
while @i<12*@hours
begin……
[/Quote]
感谢楼上各位的回答，由于我的表除了两列起止时间，后面还有列，我把
insert into #test1 (begindate,enddate)
select @begin,dateadd(mi,5,@begin)
********
改成
DECLARE @i int, @begin datetime, @hours int

SET              @begin = '2010-05-19 0:00:00'

SET              @hours = 24

SET              @i = 1 WHILE @i < 12 * @hours BEGIN

                          INSERT INTO RT_JD(Time_start, Time_end)

VALUES     (SELECT     @begin, dateadd(mi, 5, @begin))//这里提示说有错误，我不是太懂错哪里了

SET              @begin = dateadd(mi, 5, @begin)

SET              @i = @i + 1 END
dawugui 2010-05-19
  • 打赏
  • 举报
 回复 
create table tb(s1 datetime,s2 datetime)

go



declare @i as int  --循环变量

declare @j as int  --时间隔(分钟)

declare @dt as datetime  --日期

set @dt = '2010-05-19'

declare @dt1 as datetime  --开始时间

declare @dt2 as datetime  --结束时间

set @j = 30

set @i = 1



while @i <= 24*60/@j

begin

  set @dt1 = dateadd(mi,(@i-1)*@j,@dt)

  set @dt2 = dateadd(mi,@i*@j,@dt)

  insert into tb values(@dt1 , @dt2)

  set @i = @i + 1

end



select * from tb



/*

s1                                                     s2                                                     

------------------------------------------------------ ------------------------------------------------------ 

2010-05-19 00:00:00.000                                2010-05-19 00:30:00.000

2010-05-19 00:30:00.000                                2010-05-19 01:00:00.000

2010-05-19 01:00:00.000                                2010-05-19 01:30:00.000

2010-05-19 01:30:00.000                                2010-05-19 02:00:00.000

2010-05-19 02:00:00.000                                2010-05-19 02:30:00.000

2010-05-19 02:30:00.000                                2010-05-19 03:00:00.000

2010-05-19 03:00:00.000                                2010-05-19 03:30:00.000

2010-05-19 03:30:00.000                                2010-05-19 04:00:00.000

2010-05-19 04:00:00.000                                2010-05-19 04:30:00.000

2010-05-19 04:30:00.000                                2010-05-19 05:00:00.000

2010-05-19 05:00:00.000                                2010-05-19 05:30:00.000

2010-05-19 05:30:00.000                                2010-05-19 06:00:00.000

2010-05-19 06:00:00.000                                2010-05-19 06:30:00.000

2010-05-19 06:30:00.000                                2010-05-19 07:00:00.000

2010-05-19 07:00:00.000                                2010-05-19 07:30:00.000

2010-05-19 07:30:00.000                                2010-05-19 08:00:00.000

2010-05-19 08:00:00.000                                2010-05-19 08:30:00.000

2010-05-19 08:30:00.000                                2010-05-19 09:00:00.000

2010-05-19 09:00:00.000                                2010-05-19 09:30:00.000

2010-05-19 09:30:00.000                                2010-05-19 10:00:00.000

2010-05-19 10:00:00.000                                2010-05-19 10:30:00.000

2010-05-19 10:30:00.000                                2010-05-19 11:00:00.000

2010-05-19 11:00:00.000                                2010-05-19 11:30:00.000

2010-05-19 11:30:00.000                                2010-05-19 12:00:00.000

2010-05-19 12:00:00.000                                2010-05-19 12:30:00.000

2010-05-19 12:30:00.000                                2010-05-19 13:00:00.000

2010-05-19 13:00:00.000                                2010-05-19 13:30:00.000

2010-05-19 13:30:00.000                                2010-05-19 14:00:00.000

2010-05-19 14:00:00.000                                2010-05-19 14:30:00.000

2010-05-19 14:30:00.000                                2010-05-19 15:00:00.000

2010-05-19 15:00:00.000                                2010-05-19 15:30:00.000

2010-05-19 15:30:00.000                                2010-05-19 16:00:00.000

2010-05-19 16:00:00.000                                2010-05-19 16:30:00.000

2010-05-19 16:30:00.000                                2010-05-19 17:00:00.000

2010-05-19 17:00:00.000                                2010-05-19 17:30:00.000

2010-05-19 17:30:00.000                                2010-05-19 18:00:00.000

2010-05-19 18:00:00.000                                2010-05-19 18:30:00.000

2010-05-19 18:30:00.000                                2010-05-19 19:00:00.000

2010-05-19 19:00:00.000                                2010-05-19 19:30:00.000

2010-05-19 19:30:00.000                                2010-05-19 20:00:00.000

2010-05-19 20:00:00.000                                2010-05-19 20:30:00.000

2010-05-19 20:30:00.000                                2010-05-19 21:00:00.000

2010-05-19 21:00:00.000                                2010-05-19 21:30:00.000

2010-05-19 21:30:00.000                                2010-05-19 22:00:00.000

2010-05-19 22:00:00.000                                2010-05-19 22:30:00.000

2010-05-19 22:30:00.000                                2010-05-19 23:00:00.000

2010-05-19 23:00:00.000                                2010-05-19 23:30:00.000

2010-05-19 23:30:00.000                                2010-05-20 00:00:00.000



（所影响的行数为 48 行）

*/
dawugui 2010-05-19
  • 打赏
  • 举报
 回复
create table tb(s1 datetime,s2 datetime)
go

declare @i as int --循环变量
declare @j as int --时间隔(分钟)
declare @dt as datetime --日期
set @dt = '2010-05-19'
declare @dt1 as datetime --开始时间
declare @dt2 as datetime --结束时间
set @j = 5
set @i = 1

while @i <= 24*60/5
begin
set @dt1 = dateadd(mi,(@i-1)*5,@dt)
set @dt2 = dateadd(mi,@i*5,@dt)
insert into tb values(@dt1 , @dt2)
set @i = @i + 1
end

select * from tb

drop table tb

/*
s1 s2
------------------------------------------------------ ------------------------------------------------------
2010-05-19 00:00:00.000 2010-05-19 00:05:00.000
2010-05-19 00:05:00.000 2010-05-19 00:10:00.000
2010-05-19 00:10:00.000 2010-05-19 00:15:00.000
2010-05-19 00:15:00.000 2010-05-19 00:20:00.000
2010-05-19 00:20:00.000 2010-05-19 00:25:00.000
2010-05-19 00:25:00.000 2010-05-19 00:30:00.000
2010-05-19 00:30:00.000 2010-05-19 00:35:00.000
2010-05-19 00:35:00.000 2010-05-19 00:40:00.000
2010-05-19 00:40:00.000 2010-05-19 00:45:00.000
2010-05-19 00:45:00.000 2010-05-19 00:50:00.000
2010-05-19 00:50:00.000 2010-05-19 00:55:00.000
2010-05-19 00:55:00.000 2010-05-19 01:00:00.000
2010-05-19 01:00:00.000 2010-05-19 01:05:00.000
2010-05-19 01:05:00.000 2010-05-19 01:10:00.000
2010-05-19 01:10:00.000 2010-05-19 01:15:00.000
2010-05-19 01:15:00.000 2010-05-19 01:20:00.000
2010-05-19 01:20:00.000 2010-05-19 01:25:00.000
2010-05-19 01:25:00.000 2010-05-19 01:30:00.000
2010-05-19 01:30:00.000 2010-05-19 01:35:00.000
2010-05-19 01:35:00.000 2010-05-19 01:40:00.000
2010-05-19 01:40:00.000 2010-05-19 01:45:00.000
2010-05-19 01:45:00.000 2010-05-19 01:50:00.000
2010-05-19 01:50:00.000 2010-05-19 01:55:00.000
2010-05-19 01:55:00.000 2010-05-19 02:00:00.000
2010-05-19 02:00:00.000 2010-05-19 02:05:00.000
2010-05-19 02:05:00.000 2010-05-19 02:10:00.000
2010-05-19 02:10:00.000 2010-05-19 02:15:00.000
2010-05-19 02:15:00.000 2010-05-19 02:20:00.000
2010-05-19 02:20:00.000 2010-05-19 02:25:00.000
2010-05-19 02:25:00.000 2010-05-19 02:30:00.000
2010-05-19 02:30:00.000 2010-05-19 02:35:00.000
2010-05-19 02:35:00.000 2010-05-19 02:40:00.000
2010-05-19 02:40:00.000 2010-05-19 02:45:00.000
2010-05-19 02:45:00.000 2010-05-19 02:50:00.000
2010-05-19 02:50:00.000 2010-05-19 02:55:00.000
2010-05-19 02:55:00.000 2010-05-19 03:00:00.000
2010-05-19 03:00:00.000 2010-05-19 03:05:00.000
2010-05-19 03:05:00.000 2010-05-19 03:10:00.000
2010-05-19 03:10:00.000 2010-05-19 03:15:00.000
2010-05-19 03:15:00.000 2010-05-19 03:20:00.000
2010-05-19 03:20:00.000 2010-05-19 03:25:00.000
2010-05-19 03:25:00.000 2010-05-19 03:30:00.000
2010-05-19 03:30:00.000 2010-05-19 03:35:00.000
2010-05-19 03:35:00.000 2010-05-19 03:40:00.000
2010-05-19 03:40:00.000 2010-05-19 03:45:00.000
2010-05-19 03:45:00.000 2010-05-19 03:50:00.000
2010-05-19 03:50:00.000 2010-05-19 03:55:00.000
2010-05-19 03:55:00.000 2010-05-19 04:00:00.000
2010-05-19 04:00:00.000 2010-05-19 04:05:00.000
2010-05-19 04:05:00.000 2010-05-19 04:10:00.000
2010-05-19 04:10:00.000 2010-05-19 04:15:00.000
2010-05-19 04:15:00.000 2010-05-19 04:20:00.000
2010-05-19 04:20:00.000 2010-05-19 04:25:00.000
2010-05-19 04:25:00.000 2010-05-19 04:30:00.000
2010-05-19 04:30:00.000 2010-05-19 04:35:00.000
2010-05-19 04:35:00.000 2010-05-19 04:40:00.000
2010-05-19 04:40:00.000 2010-05-19 04:45:00.000
2010-05-19 04:45:00.000 2010-05-19 04:50:00.000
2010-05-19 04:50:00.000 2010-05-19 04:55:00.000
2010-05-19 04:55:00.000 2010-05-19 05:00:00.000
2010-05-19 05:00:00.000 2010-05-19 05:05:00.000
2010-05-19 05:05:00.000 2010-05-19 05:10:00.000
2010-05-19 05:10:00.000 2010-05-19 05:15:00.000
2010-05-19 05:15:00.000 2010-05-19 05:20:00.000
2010-05-19 05:20:00.000 2010-05-19 05:25:00.000
2010-05-19 05:25:00.000 2010-05-19 05:30:00.000
2010-05-19 05:30:00.000 2010-05-19 05:35:00.000
2010-05-19 05:35:00.000 2010-05-19 05:40:00.000
2010-05-19 05:40:00.000 2010-05-19 05:45:00.000
2010-05-19 05:45:00.000 2010-05-19 05:50:00.000
2010-05-19 05:50:00.000 2010-05-19 05:55:00.000
2010-05-19 05:55:00.000 2010-05-19 06:00:00.000
2010-05-19 06:00:00.000 2010-05-19 06:05:00.000
2010-05-19 06:05:00.000 2010-05-19 06:10:00.000
2010-05-19 06:10:00.000 2010-05-19 06:15:00.000
2010-05-19 06:15:00.000 2010-05-19 06:20:00.000
2010-05-19 06:20:00.000 2010-05-19 06:25:00.000
....
....
2010-05-19 23:05:00.000 2010-05-19 23:10:00.000
2010-05-19 23:10:00.000 2010-05-19 23:15:00.000
2010-05-19 23:15:00.000 2010-05-19 23:20:00.000
2010-05-19 23:20:00.000 2010-05-19 23:25:00.000
2010-05-19 23:25:00.000 2010-05-19 23:30:00.000
2010-05-19 23:30:00.000 2010-05-19 23:35:00.000
2010-05-19 23:35:00.000 2010-05-19 23:40:00.000
2010-05-19 23:40:00.000 2010-05-19 23:45:00.000
2010-05-19 23:45:00.000 2010-05-19 23:50:00.000
2010-05-19 23:50:00.000 2010-05-19 23:55:00.000
2010-05-19 23:55:00.000 2010-05-20 00:00:00.000

（所影响的行数为 288 行）

*/
cailee 2010-05-19
  • 打赏
  • 举报
 回复 
declare @t table(col varchar(20),col2 varchar(20))





declare @i int

set @i=1

declare @time datetime

set @time='2010-1-1'



while @i<12*24

begin

	insert into @t(col,col2)

	select CONVERT(char(8),@time,108),CONVERT(char(8),DATEADD(minute,5,@time),108)

	set @i=@i+1

	set @time=DATEADD(minute,10,@time)

end 



select * from @t

/*

col                  col2

-------------------- --------------------

00:00:00             00:05:00

00:10:00             00:15:00

00:20:00             00:25:00

00:30:00             00:35:00

00:40:00             00:45:00

00:50:00             00:55:00

01:00:00             01:05:00

01:10:00             01:15:00

*/
黄_瓜 2010-05-19
  • 打赏
  • 举报
 回复 
create table t(begintime varchar(5),endtime varchar(5))



declare @i int

set @i=0

--select  convert(varchar(5),dateadd(mi,@i,0),108)

while @i<60*24

begin

insert into t (begintime,endtime) select  convert(varchar(5),dateadd(mi,@i,0),108),convert(varchar(5),dateadd(mi,@i+5,0),108)

set @i=@i+5

end 

select * from t

/*

begintime endtime

--------- -------

00:00     00:05

00:05     00:10

00:10     00:15

00:15     00:20

00:20     00:25

00:25     00:30

00:30     00:35

00:35     00:40

00:40     00:45

00:45     00:50

00:50     00:55

00:55     01:00

01:00     01:05

01:05     01:10

01:10     01:15

01:15     01:20

01:20     01:25

01:25     01:30

01:30     01:35

01:35     01:40

01:40     01:45

01:45     01:50

01:50     01:55

01:55     02:00

02:00     02:05

02:05     02:10

02:10     02:15

02:15     02:20

02:20     02:25

02:25     02:30

02:30     02:35

02:35     02:40

02:40     02:45

02:45     02:50

02:50     02:55

02:55     03:00

03:00     03:05

03:05     03:10

03:10     03:15

03:15     03:20

03:20     03:25

03:25     03:30

03:30     03:35

03:35     03:40

03:40     03:45

03:45     03:50

03:50     03:55

03:55     04:00

04:00     04:05

04:05     04:10

04:10     04:15

04:15     04:20

04:20     04:25

04:25     04:30

04:30     04:35

04:35     04:40

04:40     04:45

04:45     04:50

04:50     04:55

04:55     05:00

05:00     05:05

05:05     05:10

05:10     05:15

05:15     05:20

05:20     05:25

05:25     05:30

05:30     05:35

05:35     05:40

05:40     05:45

05:45     05:50

05:50     05:55

05:55     06:00

06:00     06:05

06:05     06:10

06:10     06:15

06:15     06:20

06:20     06:25

06:25     06:30

06:30     06:35

06:35     06:40

06:40     06:45

06:45     06:50

06:50     06:55

06:55     07:00

07:00     07:05

07:05     07:10

07:10     07:15

07:15     07:20

07:20     07:25

07:25     07:30

07:30     07:35

07:35     07:40

07:40     07:45

07:45     07:50

07:50     07:55

07:55     08:00

08:00     08:05

08:05     08:10

08:10     08:15

08:15     08:20

08:20     08:25

08:25     08:30

08:30     08:35

08:35     08:40

08:40     08:45

08:45     08:50

08:50     08:55

08:55     09:00

09:00     09:05

09:05     09:10

09:10     09:15

09:15     09:20

09:20     09:25

09:25     09:30

09:30     09:35

09:35     09:40

09:40     09:45

09:45     09:50

09:50     09:55

09:55     10:00

10:00     10:05

10:05     10:10

10:10     10:15

10:15     10:20

10:20     10:25

10:25     10:30

10:30     10:35

10:35     10:40

10:40     10:45

10:45     10:50

10:50     10:55

10:55     11:00

11:00     11:05

11:05     11:10

11:10     11:15

11:15     11:20

11:20     11:25

11:25     11:30

11:30     11:35

11:35     11:40

11:40     11:45

11:45     11:50

11:50     11:55

11:55     12:00

12:00     12:05

12:05     12:10

12:10     12:15

12:15     12:20

12:20     12:25

12:25     12:30

12:30     12:35

12:35     12:40

12:40     12:45

12:45     12:50

12:50     12:55

12:55     13:00

13:00     13:05

13:05     13:10

13:10     13:15

13:15     13:20

13:20     13:25

13:25     13:30

13:30     13:35

13:35     13:40

13:40     13:45

13:45     13:50

13:50     13:55

13:55     14:00

14:00     14:05

14:05     14:10

14:10     14:15

14:15     14:20

14:20     14:25

14:25     14:30

14:30     14:35

14:35     14:40

14:40     14:45

14:45     14:50

14:50     14:55

14:55     15:00

15:00     15:05

15:05     15:10

15:10     15:15

15:15     15:20

15:20     15:25

15:25     15:30

15:30     15:35

15:35     15:40

15:40     15:45

15:45     15:50

15:50     15:55

15:55     16:00

16:00     16:05

16:05     16:10

16:10     16:15

16:15     16:20

16:20     16:25

16:25     16:30

16:30     16:35

16:35     16:40

16:40     16:45

16:45     16:50

16:50     16:55

16:55     17:00

17:00     17:05

17:05     17:10

17:10     17:15

17:15     17:20

17:20     17:25

17:25     17:30

17:30     17:35

17:35     17:40

17:40     17:45

17:45     17:50

17:50     17:55

17:55     18:00

18:00     18:05

18:05     18:10

18:10     18:15

18:15     18:20

18:20     18:25

18:25     18:30

18:30     18:35

18:35     18:40

18:40     18:45

18:45     18:50

18:50     18:55

18:55     19:00

19:00     19:05

19:05     19:10

19:10     19:15

19:15     19:20

19:20     19:25

19:25     19:30

19:30     19:35

19:35     19:40

19:40     19:45

19:45     19:50

19:50     19:55

19:55     20:00

20:00     20:05

20:05     20:10

20:10     20:15

20:15     20:20

20:20     20:25

20:25     20:30

20:30     20:35

20:35     20:40

20:40     20:45

20:45     20:50

20:50     20:55

20:55     21:00

21:00     21:05

21:05     21:10

21:10     21:15

21:15     21:20

21:20     21:25

21:25     21:30

21:30     21:35

21:35     21:40

21:40     21:45

21:45     21:50

21:50     21:55

21:55     22:00

22:00     22:05

22:05     22:10

22:10     22:15

22:15     22:20

22:20     22:25

22:25     22:30

22:30     22:35

22:35     22:40

22:40     22:45

22:45     22:50

22:50     22:55

22:55     23:00

23:00     23:05

23:05     23:10

23:10     23:15

23:15     23:20

23:20     23:25

23:25     23:30

23:30     23:35

23:35     23:40

23:40     23:45

23:45     23:50

23:50     23:55

23:55     00:00



(288 行受影响)

*/

drop table t
东那个升 2010-05-19
  • 打赏
  • 举报
 回复
while @i<12*@hours
=》while @i<=12*@hours
东那个升 2010-05-19
  • 打赏
  • 举报
 回复 


create table #test1(begindate datetime,enddate datetime)





declare @i int,@begin datetime,@hours int

set @begin='2010-05-19 1:00:00'

set @hours=24

set @i=1

while @i<12*@hours

begin

insert into #test1 (begindate,enddate)  

select @begin,dateadd(mi,5,@begin)



set @begin=dateadd(mi,5,@begin)

set @i=@i+1

end 



select * from #test1



begindate               enddate

----------------------- -----------------------

2010-05-19 01:00:00.000 2010-05-19 01:05:00.000

2010-05-19 01:05:00.000 2010-05-19 01:10:00.000

2010-05-19 01:10:00.000 2010-05-19 01:15:00.000

2010-05-19 01:15:00.000 2010-05-19 01:20:00.000

2010-05-19 01:20:00.000 2010-05-19 01:25:00.000

2010-05-19 01:25:00.000 2010-05-19 01:30:00.000

2010-05-19 01:30:00.000 2010-05-19 01:35:00.000

2010-05-19 01:35:00.000 2010-05-19 01:40:00.000



.....
htl258_Tony 2010-05-19
  • 打赏
  • 举报
 回复
你的问题是要每5钟插入一条记录还是插入的记录值间隔是5分钟？
Linux必备命令（必须掌握的操作，极实用）
---- 解决工作中80%的问题，收效极高，只需要花极少的时间、一杯奶茶的钱，强力推荐。   本课程列出的都是本人日常工作中经常用到的命令，实用性极高，都有实例，拿来就可以用。 本文档可以收藏起来，以后随时...
SQL 插入一列数据
关键技术：插入一列数据，进行增删查改 –脚本 –查看是否有‘XXX’表的存在，如果有就删除表 IF OBJECT_ID (N’XXX’, N’U’) IS NOT NULL Drop Table XXX –创建表格 Create Table XXX (iD int Primary ...
DataFrame插入一列数据
在最左侧插入一列数据 将dataframe的时间列插入到imputation的第一列中 # 第一个参数指插入的位置，0表示第一列 # 第二个参数指这一列的名字 # 第三个参数为插入的数据 imputation.insert(0, '时间', dataframe[...
oracle怎么用命令向建好的表中插入一列并使它成为第一列？
oracle怎么用命令向建好的表中插入一列并使它成为第一列？请说详细点，最好举例说明。 &nbsp;分享|&nbsp;2012-05-11 14:53li597600208man&nbsp;|&nbsp;浏览 1686 次 2012-05-11 17:14 提问者采纳...
mysql 一列加一列_想在mysql数据库中的表中插入一列，怎么做？
当进行 加列操作 时，所有的数据行 都必须要 增加一段数据(图中的 列 4 数据)如上一期图解所讲，当改变数据行的长度，就需要 重建表空间(图中灰蓝的部分为发生变更的部分)数据字典中的列定义也会被更新以上操作的...
MS-SQL Server

34,593

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章