字段
下班3
2010-06-07 23:30
2010-06-07 23:35
2010-06-08 00:02
2010-06-08 00:06
2010-06-08 00:15
2010-06-07 23:10
2010-06-07 23:01如果我想把这个下班时间 23:00到次日凌晨01:00前的时间,改成在‘21:00’--‘21:30’范围内随机产生一个时间,0点以前的日期不变,0点以后的日期要减1。
这个又当如何写SQL语句?
如果时间段‘21:00’-‘21:30’生成23:00--01:00 ,又该如何谢谢!
dateadd(hh,-3,'2010-09-18 23:36:20')---时间不介于21:00--22:0000:00-1:00
dateadd(hh,9,'2010-09-18 00:36:20') ---时间不介于21:00--22:00
go
create table #tb( dt smalldatetime)
insert #tb select '2010-06-07 00:00'
insert #tb select '2010-06-07 01:00'
insert #tb select '2010-06-07 08:10'
insert #tb select '2010-06-07 08:12'
insert #tb select '2010-06-07 08:13'
insert #tb select '2010-06-07 08:06'
insert #tb select '2010-06-07 08:09'
insert #tb select '2010-06-07 09:10'
insert #tb select '2010-06-07 09:00'
insert #tb select '2010-06-07 23:00'
insert #tb select '2010-06-07 23:59'
insert #tb select '2010-06-08 08:10'
insert #tb select '2010-06-08 08:12'select cast(substring(convert(varbinary(8),dt),1,2)
+substring(convert(varbinary(8),ABS(CHECKSUM(NEWID()))%59+1380),3,2)
as smalldatetime)
from #tb
where convert(int,substring(convert(varbinary(8),dt),3,2))between 480 and 510
union all
select cast(substring(convert(varbinary(8),dateadd(dd,1,dt)),1,2)
+substring(convert(varbinary(8),ABS(CHECKSUM(NEWID()))%60),3,2)
as smalldatetime)
from #tb
where convert(int,substring(convert(varbinary(8),dt),3,2))between 480 and 510/*
-----------------------
2010-06-07 23:34:00
2010-06-07 23:19:00
2010-06-08 23:45:00
2010-06-08 00:46:00
2010-06-08 00:29:00
2010-06-09 00:35:00(6 行受影响)*/
+substring(convert(varbinary(8),ABS(CHECKSUM(NEWID()))%59+1380),3,2)
as smalldatetime)
from #tb
where convert(int,substring(convert(varbinary(8),dt),3,2))between 480 and 510
--这里注释一下 这个数字480、510、1380等是 小时*60+分钟 480=60*8(八点) 510=60*8+30(八点半)
http://topic.csdn.net/u/20100917/11/460502f7-f21c-4ccb-a404-dbcd3df039a9.html
create table #tb( dt datetime)
insert #tb select '2010-06-07 23:30'
insert #tb select '2010-06-07 23:35'
insert #tb select '2010-06-08 00:02'
insert #tb select '2010-06-08 00:06'
insert #tb select '2010-06-08 00:15'
insert #tb select '2010-06-07 23:10'
insert #tb select '2010-06-07 23:01'select dt,
dt1 =
case
when CONVERT(char(5),dt,108)>'23:00' and CONVERT(char(5),dt,108)<'23:59' then
DATEADD(second,ABS(CHECKSUM(NEWID())) % DATEDIFF(second,'2010-06-07 21:00','2010-06-07 21:30'), CONVERT(char(8),dt,112) + ' 21:00')
else
DATEADD(second,ABS(CHECKSUM(NEWID())) % DATEDIFF(second,'2010-06-07 21:00','2010-06-07 21:30'), CONVERT(char(8),dt-1,112) + ' 21:00')
end
from #tb
------------------------------------------------------ ------------------------------------------------------
2010-06-07 23:30:00.000 2010-06-07 21:20:42.000
2010-06-07 23:35:00.000 2010-06-07 21:10:20.000
2010-06-08 00:02:00.000 2010-06-07 21:14:34.000
2010-06-08 00:06:00.000 2010-06-07 21:03:41.000
2010-06-08 00:15:00.000 2010-06-07 21:15:40.000
2010-06-07 23:10:00.000 2010-06-07 21:03:45.000
2010-06-07 23:01:00.000 2010-06-07 21:26:47.000
我刚才考虑太多跨天问题,其实也不用想太多,方法还有好几种,谢谢各位提供思路。