我想把整个月的日期增加到一个表,单条很容易,如果是整个月的我不知道增加了,麻烦各位帮忙一下。
我的表结构是这样的:
WorkDateTime (datetime型) ClassID EditedBy UpdateStamp
2011-06-01 00:00:00.000 1 ADMINISTRATOR 0x0000000000032D69比如我一次性把整个7月的日期插入进去如何修改?
我的表结构是这样的:
WorkDateTime (datetime型) ClassID EditedBy UpdateStamp
2011-06-01 00:00:00.000 1 ADMINISTRATOR 0x0000000000032D69比如我一次性把整个7月的日期插入进去如何修改?
from master..spt_values
where type='P' and dateadd(dd,number,'2011-7-1')<='2011-7-31'
GO
CREATE TABLE TB(
COL_DATE DATETIME
)
GO
DECLARE @YEAR INT, @MONTH INT
SELECT @YEAR=YEAR(GETDATE()),@MONTH=MONTH(GETDATE())
--插入当前月的所有日期
INSERT INTO TB
SELECT DATEADD(DAY,NUMBER,LTRIM(@YEAR)+'-'+LTRIM(@MONTH)+'-1')
FROM MASTER..SPT_VALUES
WHERE TYPE='P' AND MONTH(DATEADD(DAY,NUMBER,LTRIM(@YEAR)+'-'+LTRIM(@MONTH)+'-1'))=@MONTH AND YEAR(DATEADD(DAY,NUMBER,LTRIM(@YEAR)+'-'+LTRIM(@MONTH)+'-1'))=@YEARSELECT * FROM TB
/*
COL_DATE
-----------------------
2011-06-01 00:00:00.000
2011-06-02 00:00:00.000
2011-06-03 00:00:00.000
2011-06-04 00:00:00.000
2011-06-05 00:00:00.000
2011-06-06 00:00:00.000
2011-06-07 00:00:00.000
2011-06-08 00:00:00.000
2011-06-09 00:00:00.000
2011-06-10 00:00:00.000
2011-06-11 00:00:00.000
2011-06-12 00:00:00.000
2011-06-13 00:00:00.000
2011-06-14 00:00:00.000
2011-06-15 00:00:00.000
2011-06-16 00:00:00.000
2011-06-17 00:00:00.000
2011-06-18 00:00:00.000
2011-06-19 00:00:00.000
2011-06-20 00:00:00.000
2011-06-21 00:00:00.000
2011-06-22 00:00:00.000
2011-06-23 00:00:00.000
2011-06-24 00:00:00.000
2011-06-25 00:00:00.000
2011-06-26 00:00:00.000
2011-06-27 00:00:00.000
2011-06-28 00:00:00.000
2011-06-29 00:00:00.000
2011-06-30 00:00:00.000
*/
(
WorkDateTime DATETIME,
ClassID INT,
EditedBy VARCHAR(50),
UpdateStamp TIMESTAMP
)
GO
--SQL
DECLARE @month INT
DECLARE @year INT
SET @year = 2011
SET @month = 7INSERT #temp
(
WorkDateTime,
ClassID,
EditedBy
)
SELECT TOP(DATEDIFF(DAY, '2011-'+ CAST(@month AS VARCHAR(10)) +'-1', DATEADD(MONTH, 1, '2011-'+ CAST(@month AS VARCHAR(10)) +'-1')))
DATEADD(DAY, number, CAST(@year AS VARCHAR(4))+'-'+ CAST(@month AS VARCHAR(10)) +'-1'),
1,
'ADMINISTRATOR'
FROM master.dbo.spt_values
WHERE [type] = 'P'
DECLARE @MyCounter INT declare @the_date datetime SET @MyCounter = 0 SET @the_date = '2011-07-01' WHILE (@MyCounter < 200000) BEGIN WAITFOR DELAY '000:00:10'
insert into WorkClass (WorkDateTime, ClassID, EditedBy, EditedDateTime)values('',@the_date) SET @the_date = @the_date + 1 SET @MyCounter = @MyCounter + 1 END
administor 1,最后那个字段是:timestamp型的。
select dateadd(dd,number,'2011-7-1'),1,'ADMINISTRATOR'
from master..spt_values
where type='P' and dateadd(dd,number,'2011-7-1')<='2011-7-31'
消息 207,级别 16,状态 3,第 1 行
列名 'type' 无效。
这个是什么意思?
select dateadd(dd,number,'2011-07-01'),1,'ADMINISTRATOR'
from master..spt_values
where type='P' and dateadd(dd,number,'2011-07-01')<='2011-07-31'
这个可以,但是我有的不是很明白,比如说:
master..spt_values
这个是什么意思?
分不够可以加,呵呵
是master.dbo.spt_values
就是指数据库master下的表spt_values
sql2005中从0-2047
DECLARE @year INT
SET @year = 2011
SET @month = 7INSERT #temp
(
WorkDateTime,
ClassID,
EditedBy
)
SELECT TOP(DATEDIFF(DAY, '2011-'+ CAST(@month AS VARCHAR(10)) +'-1', DATEADD(MONTH, 1, '2011-'+ CAST(@month AS VARCHAR(10)) +'-1')))
DATEADD(DAY, number, CAST(@year AS VARCHAR(4))+'-'+ CAST(@month AS VARCHAR(10)) +'-1'),
1,
'ADMINISTRATOR'
FROM master.dbo.spt_values
WHERE [type] = 'P'豆子这个可以怎么修改?
DECLARE @year INT
SET @year = 2011
SET @month = 7select dateadd(dd,number,ltrim(@year)+'-'+ltrim(@month)+'-1'),'ADMINISTRATOR',1
from master..spt_values
where type='P'
and dateadd(dd,number,ltrim(@year)+'-'+ltrim(@month)+'-1')
<dateadd(mm,1,ltrim(@year)+'-'+ltrim(@month)+'-1')