34,838
社区成员




--插入指定日期及以前的该月的所有日期
declare @d datetime
set @d='2008-05-22'
create table 表(数值 int,日期 datetime)
select top 31 id=identity(int,1,1) into # from sysobjects
insert into 表(数值,日期) select 999,convert(datetime,convert(varchar(8),@d,120)+right('0'+convert(varchar(2),id),2)) from # where id<=datepart(d,@d)
select * from 表
go
drop table 表,#
/*
数值 日期
----------- -----------------------
999 2008-05-01 00:00:00.000
999 2008-05-02 00:00:00.000
999 2008-05-03 00:00:00.000
999 2008-05-04 00:00:00.000
999 2008-05-05 00:00:00.000
999 2008-05-06 00:00:00.000
999 2008-05-07 00:00:00.000
999 2008-05-08 00:00:00.000
999 2008-05-09 00:00:00.000
999 2008-05-10 00:00:00.000
999 2008-05-11 00:00:00.000
999 2008-05-12 00:00:00.000
999 2008-05-13 00:00:00.000
999 2008-05-14 00:00:00.000
999 2008-05-15 00:00:00.000
999 2008-05-16 00:00:00.000
999 2008-05-17 00:00:00.000
999 2008-05-18 00:00:00.000
999 2008-05-19 00:00:00.000
999 2008-05-20 00:00:00.000
999 2008-05-21 00:00:00.000
999 2008-05-22 00:00:00.000
(22 行受影响)
*/
--插入本月今天及以前的所有日期
create table 表(数值 int,日期 datetime)
select top 31 id=identity(int,1,1) into # from sysobjects
insert into 表(数值,日期) select 999,convert(datetime,convert(varchar(8),getdate(),120)+right('0'+convert(varchar(2),id),2)) from # where id<=datepart(d,getdate())
select * from 表
go
drop table 表,#
/*
数值 日期
----------- -----------------------
999 2008-09-01 00:00:00.000
999 2008-09-02 00:00:00.000
999 2008-09-03 00:00:00.000
999 2008-09-04 00:00:00.000
999 2008-09-05 00:00:00.000
999 2008-09-06 00:00:00.000
999 2008-09-07 00:00:00.000
999 2008-09-08 00:00:00.000
999 2008-09-09 00:00:00.000
999 2008-09-10 00:00:00.000
999 2008-09-11 00:00:00.000
999 2008-09-12 00:00:00.000
999 2008-09-13 00:00:00.000
999 2008-09-14 00:00:00.000
999 2008-09-15 00:00:00.000
(15 行受影响)
*/
/*------------------------
create table 表(数值 int,日期 datetime)
select * from 表
declare @starttime datetime
declare @endtime datetime
set @starttime = '2008-09-01'
set @endtime = '2008-09-15'
while @starttime<=@endtime
begin
insert into 表
select 1,@starttime
set @starttime = dateadd(day,1,@starttime)
end
select * from 表
drop table 表
------------------------*/
数值 日期
----------- -----------------------
(0 行受影响)
(1 行受影响)
(1 行受影响)
(1 行受影响)
(1 行受影响)
(1 行受影响)
(1 行受影响)
(1 行受影响)
(1 行受影响)
(1 行受影响)
(1 行受影响)
(1 行受影响)
(1 行受影响)
(1 行受影响)
(1 行受影响)
(1 行受影响)
数值 日期
----------- -----------------------
1 2008-09-01 00:00:00.000
1 2008-09-02 00:00:00.000
1 2008-09-03 00:00:00.000
1 2008-09-04 00:00:00.000
1 2008-09-05 00:00:00.000
1 2008-09-06 00:00:00.000
1 2008-09-07 00:00:00.000
1 2008-09-08 00:00:00.000
1 2008-09-09 00:00:00.000
1 2008-09-10 00:00:00.000
1 2008-09-11 00:00:00.000
1 2008-09-12 00:00:00.000
1 2008-09-13 00:00:00.000
1 2008-09-14 00:00:00.000
1 2008-09-15 00:00:00.000
(15 行受影响)
--不知道你的数值列怎么来,我都插入的1
create table 表(数值 int,日期 datetime)
select * from 表
declare @starttime datetime
declare @endtime datetime
set @starttime = '2008-09-01'
set @endtime = '2008-09-15'
while @starttime<=@endtime
begin
insert into 表
select 1,@starttime
set @starttime = dateadd(day,1,@starttime)
end
select * from 表
drop table 表
DECLARE @SDate DATETIME
SET @SDate='2008-9-1'
INSERT INTO 表(数值,日期)
SELECT 0 as 数值,dateadd(day,a+10*b,@SDate) as 日期
FROM (
select 0 as a union all
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9
) as t1,(
select 0 as b union all
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9
) as t2
WHERE a+10*b<15
select top 31 id=identity(int,1,1) into # from sysobjects
insert into 表(日期) select convert(varchar(8),getdate(),120)+right('0'+convert(varchar(2),id),2) from # where id<=15
insert into 表 values() from 表 where 日期 between... and...
DECLARE @SetDate1 DATETIME,@SetDate2 DATETIME
SET @SetDate1='2008-9-1'
SET @setDATE2='2008-9-15'
INSERT INTO 表(数值,日期)
SELECT 数值,日期 FROM 表 WHERE 日期 BETWEEN @SetDate1 AND @SetDate2