IF OBJECT_ID('TB')IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(ID INT IDENTITY,[DATE] DATETIME)
INSERT TB SELECT NULL
INSERT TB SELECT NULL
INSERT TB SELECT NULL
INSERT TB SELECT NULL
INSERT TB SELECT NULL
INSERT TB SELECT NULL
INSERT TB SELECT '2008-01-01'
INSERT TB SELECT GETDATE()
DECLARE @DT DATETIME
SET @DT='2006-01-01'
UPDATE TB SET [DATE]=DATEADD(DAY,ID,@DT) WHERE [DATE] IS NULL
SELECT * FROM TB
/*ID DATE
----------- ------------------------------------------------------
1 2006-01-02 00:00:00.000
2 2006-01-03 00:00:00.000
3 2006-01-04 00:00:00.000
4 2006-01-05 00:00:00.000
5 2006-01-06 00:00:00.000
6 2006-01-07 00:00:00.000
7 2008-01-01 00:00:00.000
8 2009-02-02 13:49:27.387*/
GO
CREATE TABLE TB(ID INT IDENTITY,[DATE] DATETIME)
INSERT TB SELECT NULL
INSERT TB SELECT NULL
INSERT TB SELECT NULL
INSERT TB SELECT NULL
INSERT TB SELECT NULL
INSERT TB SELECT NULL
INSERT TB SELECT '2008-01-01'
INSERT TB SELECT GETDATE()
DECLARE @DT DATETIME
SET @DT='2006-01-01'
UPDATE TB SET [DATE]=DATEADD(DAY,ID,@DT) WHERE [DATE] IS NULL
SELECT * FROM TB
/*ID DATE
----------- ------------------------------------------------------
1 2006-01-02 00:00:00.000
2 2006-01-03 00:00:00.000
3 2006-01-04 00:00:00.000
4 2006-01-05 00:00:00.000
5 2006-01-06 00:00:00.000
6 2006-01-07 00:00:00.000
7 2008-01-01 00:00:00.000
8 2009-02-02 13:49:27.387*/
SELECT a=DATEADD(day, -1, getdate()) 当中的-1用倒数第二的id减去最后一条的id得到。你需要补齐时间的记录一共有多少条?
如果多的话,把day换成Hour甚至second
getdate()应该是你最后一条的时间。
IF OBJECT_ID('TB')IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(ID INT IDENTITY,[DATE] DATETIME)
INSERT TB SELECT NULL
INSERT TB SELECT NULL
INSERT TB SELECT NULL
INSERT TB SELECT NULL
INSERT TB SELECT NULL
INSERT TB SELECT NULL
INSERT TB SELECT '2008-01-01'
INSERT TB SELECT GETDATE()
DECLARE @DT DATETIME
SET @DT='2006-01-01'
UPDATE TB SET [DATE]=DATEADD(MI,ID,@DT) WHERE [DATE] IS NULL
SELECT * FROM TB
/*ID DATE
----------- ------------------------------------------------------
1 2006-01-01 00:01:00.000
2 2006-01-01 00:02:00.000
3 2006-01-01 00:03:00.000
4 2006-01-01 00:04:00.000
5 2006-01-01 00:05:00.000
6 2006-01-01 00:06:00.000
7 2008-01-01 00:00:00.000
8 2009-02-02 14:14:00.050*/
1:统计此表记录数
2:从你有时间记录的其他表中找出相同时间区间的记录
3:把找出的时间写到你的目标表记录中
首先很谢谢你,但是确实没有别的表里有id可以和这个表中的ID对应,如果有时间就不用那么麻烦了
GO
CREATE TABLE TB(ID INT IDENTITY,[DATE] DATETIME)
INSERT TB SELECT NULL
INSERT TB SELECT NULL
INSERT TB SELECT NULL
INSERT TB SELECT '2007-1-1'
INSERT TB SELECT NULL
INSERT TB SELECT NULL
INSERT TB SELECT '2008-01-01'
INSERT TB SELECT NULL
INSERT TB SELECT NULL
INSERT TB SELECT GETDATE()DECLARE @DT DATETIMESET @DT='2006-01-01'DECLARE @XDT DATETIME
set @XDT = @DT
update A set
@XDT = DATEADD(ms,abs(CHECKSUM(NEWID())),isnull(b.[date],@xdt)),
[DATE]=@xdt
from TB A
left join TB B
on B.ID = (select MAX(id) from TB where ID < A.ID and [DATE] is not null)
WHERE a.[DATE] IS NULL
SELECT * FROM TB--结果
ID DATE
----------- -----------------------
1 2006-01-09 12:21:27.207
2 2006-01-28 01:08:51.680
3 2006-02-03 21:03:31.950
4 2007-01-01 00:00:00.000
5 2007-01-07 18:46:28.607
6 2007-01-20 12:17:00.433
7 2008-01-01 00:00:00.000
8 2008-01-13 12:59:20.343
9 2008-01-12 12:19:26.023
10 2009-02-02 14:57:09.970(10 行受影响)
SET @DT='2006-01-01'update TB set
@DT = DATEADD(ms,cast(cast(DATEDIFF(day,'2006-1-1','2009-1-1') as bigint)*24*60*60*1000/2000000 as int),@dt),
[DATE]=@dt
DROP TABLE tb
GO
--生成测试数据
SELECT TOP 10 IDENTITY(INT) id,CAST('1900-1-1' AS DATETIME) d INTO tb FROM sys.objects,sys.columnsGO
SELECT * FROM Tb
/*
在你的问题上1900-1-1你当它是null好了。
1 1900-01-01 00:00:00.000
2 1900-01-01 00:00:00.000
3 1900-01-01 00:00:00.000
4 1900-01-01 00:00:00.000
5 1900-01-01 00:00:00.000
6 1900-01-01 00:00:00.000
7 1900-01-01 00:00:00.000
8 1900-01-01 00:00:00.000
9 1900-01-01 00:00:00.000
10 1900-01-01 00:00:00.000
*/--因为你id与d的大小走向相同DECLARE @d DATETIME,@n INT
SET @d='2006-1-1'
SET @n=DATEDIFF(dd,@d,'2009-2-1')
SELECT @n = @n/COUNT(*) FROM tb--因为我的测试表数据太少,只有10条,所以以dd为单位。你自己处理的话,按这个算法改为分钟处理就可以了,绝对随机
UPDATE tb SET d=DATEADD(dd,CAST(RAND(CHECKSUM(NEWID())) * @n + (id-1)*@n AS INT),@d)SELECT * FROM tb/*
1 2006-04-12 00:00:00.000
2 2006-07-04 00:00:00.000
3 2006-07-13 00:00:00.000
4 2007-03-10 00:00:00.000
5 2007-02-27 00:00:00.000
6 2007-01-09 00:00:00.000
7 2006-02-10 00:00:00.000
8 2008-05-13 00:00:00.000
9 2006-04-02 00:00:00.000
10 2007-04-23 00:00:00.000
*/GODROP TABLE tb
GO
试试这个
IF OBJECT_ID('TB')IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(ID INT IDENTITY,[DATE] DATETIME)
INSERT TB SELECT NULL
INSERT TB SELECT NULL
INSERT TB SELECT NULL
INSERT TB SELECT NUll
INSERT TB SELECT NULL
INSERT TB SELECT NULL
INSERT TB SELECT NULL
INSERT TB SELECT NULL
INSERT TB SELECT NULL
INSERT TB SELECT NULLDECLARE @BeginDate DATETIME,@EndDate DateTime ,@aa float,@minID int,@Row int
SET @BeginDate='2006-01-01'
set @EndDate = getdate()
select @Row = count(1) from tb where date is null
select @minID= Min(id) from tb where date is null
set @aa = datediff(ss,@BeginDate,@EndDate)/@Row
update tb set date = dateadd(ss,(id-@minID)*@aa + ceiling(RAND(CHECKSUM(NEWID())) * @aa ),@BeginDate)select * from tbID DATE
----------- -----------------------
1 2006-03-23 16:16:05.000
2 2006-07-27 09:12:37.000
3 2006-11-23 14:45:51.000
4 2007-01-08 02:11:44.000
5 2007-07-15 06:22:07.000
6 2007-10-04 21:51:26.000
7 2008-02-13 07:27:23.000
8 2008-05-18 09:52:24.000
9 2008-07-15 01:24:31.000
10 2008-11-12 05:25:07.000(10 row(s) affected)