求助sql中的随机日期或随机时间

[肖恩]

create table datetimes
(
id int not null primary key identity(1,1),
dates datetime not null
)

要求向数据库表中插入100给随机日期或随机时间，
哪位大哥帮帮忙！！！

[Sharon_liu]

随机简单，均匀分布就不好做了。。。

[中国风]

沒有範圍?
用一個臨時表記錄取值，每次用Order by newID()+Top 1，取一次在表刪除對應記錄

或用newID()--轉換取

[Sharon_liu]

好了，这样写

DECLARE @I INT
SELECT @I=1
WHILE @I<=100
BEGIN
INSERT INTO DATETIMES(DATES) SELECT SELECT CAST(ABS(checksum(NEWID()))%3012153-53690 AS DATETIME)
SELECT @I=@I+1
END

[中国风]

SELECT 
TOP 100
ROW_NUMBER()OVER(ORDER BY a.ID) AS ID,
CAST(CHECKSUM(newID())%40357 AS DATETIME)+RAND() AS 隨機值
FROM syscolumns a,syscolumns b

舉個例子給樓主

[东那个升]

[振乾]

[Quote=引用 4 楼 roy_88 的回复:]
SQL code
SELECT
TOP 100
ROW_NUMBER()OVER(ORDER BY a.ID) AS ID,
CAST(CHECKSUM(newID())%40357 AS DATETIME)+RAND() AS 隨機值
FROM syscolumns a,syscolumns b


舉個例子給樓主
[/Quote]

顶这个。

[feilniu]

生成指定日期范围内的100个随机日期（精确到秒）：

DECLARE @BDate datetime, @EDate datetime
SET @BDate = '20090101'          --下限
SET @EDate = '20101231 23:59:59' --上限
SELECT n, random_datetime = DATEADD(second,ABS(CHECKSUM(NEWID()))%DATEDIFF(second,@BDate,@Edate),@BDate)
FROM dbo.Nums
WHERE n BETWEEN 1 AND 100

[中国风]

DECLARE @dt1 DATETIME,@dt2 DATETIME,@a BIGINT,@b BIGINT
SET @dt1='2010-01-01'--開始日期
SET @dt2='2010-06-30'--結束日期

SELECT @a=CAST(@dt1 AS BIGINT),@b=CAST(@dt2-@dt1 AS BIGINT)

DECLARE @t TABLE(ID INT,dt datetime)

DECLARE @i INT
SET @i=0

WHILE @i<100
BEGIN
	INSERT @t
	SELECT 
		@i+1,CAST(ABS(CHECKSUM(newID()))%@b+@a AS DATETIME)+RAND() 
	SET @i=@i+1
end
SELECT * FROM @t

[肖恩]

能不能告诉下小弟具体怎么生成随机时间，有无范围都可！！谢谢！！

[中国风]

生成結果

ID	dt
1	2010-06-16 15:36:36.523
2	2010-01-21 18:17:09.197
3	2010-03-02 16:59:17.277
4	2010-06-11 18:51:12.910
5	2010-04-07 20:35:42.130
6	2010-04-27 00:07:22.510
7	2010-02-15 09:52:53.923
8	2010-03-09 21:57:53.083
9	2010-01-22 16:07:33.960
10	2010-01-10 18:10:42.313
11	2010-06-21 00:37:49.937
12	2010-04-03 05:17:44.067
13	2010-01-05 06:52:26.233
14	2010-06-08 12:03:34.990
15	2010-03-15 11:44:19.763
16	2010-04-13 02:08:58.700
17	2010-04-15 07:40:40.693
18	2010-04-17 19:37:52.740
19	2010-03-09 13:09:44.103
20	2010-01-23 07:58:20.443
21	2010-04-03 20:06:46.080
22	2010-02-03 23:42:22.193
23	2010-06-25 12:46:54.733
24	2010-02-21 20:21:18.473
25	2010-03-30 05:43:27.363
26	2010-03-19 16:15:59.073
27	2010-03-05 14:48:43.170
28	2010-05-18 03:46:04.377
29	2010-06-15 18:16:40.303
30	2010-03-17 04:46:50.530
31	2010-05-30 14:54:40.420
32	2010-01-18 00:18:23.147
33	2010-03-14 04:34:30.803
34	2010-06-24 22:25:51.077
35	2010-01-20 05:19:46.033
36	2010-02-01 19:38:25.697
37	2010-03-16 15:31:54.500
38	2010-01-01 23:44:23.863
39	2010-04-25 18:02:29.130
40	2010-02-03 12:44:42.753
41	2010-05-15 08:45:47.083
42	2010-03-25 20:38:15.733
43	2010-05-11 05:25:11.420
44	2010-03-13 03:04:56.363
45	2010-06-07 15:11:29.947
46	2010-05-08 17:16:38.230
47	2010-01-25 06:18:41.160
48	2010-04-16 23:18:31.973
49	2010-04-20 17:55:07.373
50	2010-05-09 16:57:42.597
51	2010-03-12 21:15:33.467
52	2010-03-21 10:06:12.207
53	2010-02-19 19:45:20.560
54	2010-03-15 14:41:31.807
55	2010-01-11 00:04:14.203
56	2010-03-23 14:20:35.047
57	2010-04-24 09:08:17.413
58	2010-01-30 04:37:41.183
59	2010-03-25 14:52:51.250
60	2010-01-04 10:32:59.550
61	2010-03-08 22:39:25.747
62	2010-06-25 22:21:23.500
63	2010-06-04 21:37:02.120
64	2010-01-20 07:06:07.073
65	2010-02-11 19:04:24.453
66	2010-03-12 16:11:37.037
67	2010-03-23 23:43:01.703
68	2010-06-10 23:28:55.660
69	2010-05-29 11:11:15.550
70	2010-06-21 19:08:21.953
71	2010-04-25 03:01:42.020
72	2010-06-18 15:24:32.807
73	2010-04-04 04:41:19.303
74	2010-03-11 09:33:14.940
75	2010-02-24 20:35:06.610
76	2010-01-15 10:40:18.713
77	2010-05-18 00:28:38.863
78	2010-02-19 02:43:42.533
79	2010-06-20 23:05:00.823
80	2010-03-04 03:11:17.437
81	2010-06-16 03:07:02.017
82	2010-01-31 13:44:06.087
83	2010-06-20 19:57:47.093
84	2010-05-05 11:09:50.230
85	2010-01-05 02:09:24.407
86	2010-03-11 00:30:49.057
87	2010-06-13 19:59:40.163
88	2010-04-16 11:58:44.060
89	2010-06-21 12:08:49.887
90	2010-03-05 16:34:16.753
91	2010-05-20 06:28:16.473
92	2010-03-23 12:54:01.223
93	2010-06-08 07:53:26.757
94	2010-01-15 18:51:13.257
95	2010-01-05 15:37:13.523
96	2010-02-17 02:06:43.080
97	2010-04-11 23:53:15.840
98	2010-04-01 05:13:25.753
99	2010-03-29 20:13:35.363
100	2010-02-16 06:06:12.003

[中国风]

[Quote=引用 9 楼 zl2121016 的回复:]

能不能告诉下小弟具体怎么生成随机时间，有无范围都可！！谢谢！！
[/Quote]
參照8樓提供的方法

[Sharon_liu]

大版也抢分
我写的也没错都顶大版去了

[中国风]

[Quote=引用 12 楼 sharon_liu 的回复:]

大版也抢分
我写的也没错都顶大版去了
[/Quote]
沒搶，看到有點意思就回一下

[肖恩]

OK 解决了 谢谢各位 都有份

[中国风]

樓主看看，上面結果改了一下，rand()--只能一個個搶隨機

把結果存在一個表變量里，在熱循環處自定義規則，如：加上判斷1日只可取一次等等

[qgqch2008]

[Quote=引用 12 楼 sharon_liu 的回复:]
大版也抢分
我写的也没错都顶大版去了
[/Quote]
我顶你~~