34,571
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE #(COL CHAR(8))
DECLARE @DATE DATETIME
SET @DATE='1900-1-1 00:00:00'
WHILE @DATE<='1900-1-1 23:59:59'
BEGIN
INSERT INTO # SELECT CONVERT(VARCHAR(12),@DATE,108)
SET @DATE=DATEADD(S,1,@DATE)
END
SELECT * FROM #
/*
00:00:00
00:00:01
00:00:02
00:00:03
00:00:04
00:00:05
........
23:59:54
23:59:55
23:59:56
23:59:57
23:59:58
23:59:59
*/
select top (24*60*60-1) id=identity(int,1,1) into #1 from sys.syscolumns a ,sys.syscolumns b
select convert(varchar(20),dateadd(s,id,0),108) from #1
create table test(t varchar(20))
declare @d datetime
set @d = '2000-01-01 00:00:00'
select dateadd(ss,2,@d)
while(@d<'2000-01-02 00:00:00')
begin
insert test select @d
set @d = dateadd(ss,1,@d)
end
--try:
declare @tb table(dt varchar(8))
declare @dt datetime
set @dt=''
while convert(varchar(8),@dt,108)<='23:59:59'
begin
insert @tb select convert(varchar(8),@dt,108)
set @dt=dateadd(ss,1,@dt)
end
select * from @tb
DECLARE @INT INT
SET @INT=1
DECLARE @TIME DATETIME
SET @TIME=CONVERT(DATETIME,'00:00:00')
WHILE @INT<=(SELECT DATEDIFF(SS,CONVERT(DATETIME,'00:00:00'),CONVERT(DATETIME,'23:59:59')))
SET @TIME=DATEADD(SS,1,CONVERT(DATETIME,'00:00:00'))
SELECT @TIME
SET @INT=@INT+1
set nocount on
declare @s table(dt datetime)
declare @t datetime
set @t='00:00:00'
while @t<'23:59:59 '
begin
insert @s select @t
set @t =dateadd(ss,1,@t)
end
select top 10 * from @s
/*
dt
-----------------------
1900-01-01 00:00:00.000
1900-01-01 00:00:01.000
1900-01-01 00:00:02.000
1900-01-01 00:00:03.000
1900-01-01 00:00:04.000
1900-01-01 00:00:05.000
1900-01-01 00:00:06.000
1900-01-01 00:00:07.000
1900-01-01 00:00:08.000
1900-01-01 00:00:09.000
*/