DECLARE @T1 DATETIME DECLARE @T2 DATETIME SET @T1='2012-12-25 00:00:00.000' SET @T2='2012-12-30 18:30:21.542' declare @i int select @i =Datediff(HOUR ,@T1,@T2) if(@i>83) begin set @T1 = DATEADD(HOUR,left(RAND()*18,2),@T1) print @T1 end
随机数搞错了 DECLARE @T1 DATETIME DECLARE @T2 DATETIME SET @T1='2012-12-25 00:00:00.000' SET @T2='2012-12-30 18:30:21.542' declare @i int select @i =Datediff(HOUR ,@T1,@T2) if(@i>83) begin set @T1 = DATEADD(HOUR, 7+cast(left(RAND()*11,1) as int),@T1) print @T1 end
--不大明白啊,那你把@t1改成在@t2的基础上加减三天的一个随机数,再处理小时问题 DECLARE @T1 DATETIME DECLARE @T2 DATETIME SET @T1='2012-12-25 00:00:00.000' SET @T2='2012-12-30 18:30:21.542' declare @i int select @i =Datediff(HOUR ,@T1,@T2) if(@i>83) begin
set @T1 = DATEADD(DAY, -cast(left(4*RAND(),1) as int),convert(varchar(20),@T2,23)) set @T1 = DATEADD(HOUR, 7+cast(left(RAND()*11,1) as int),@T1) select @T1 select Datediff(HOUR ,@T1,@T2) end
--比如: DECLARE @T1 DATETIME DECLARE @T2 DATETIME SET @T1='2012-12-25 00:00:00.000' SET @T2='2012-12-30 18:30:21.542'DECLARE @T3 DateTime SET @T3='2012-12-30 15:30:21.542' if DATEDIFF(HOUR,@T1,@T2)>83 and DATEPART(HOUR,@T3)>7 and DATEPART(HOUR,@T3)<18 begin select @T1 set @T1='2012-12-25 13:00:00.000' endselect @T1
--笨办法,求简化 DECLARE @T1 DATETIME DECLARE @T2 DATETIME DECLARE @N INT SET @T1='2012-12-25 00:00:00.000' SET @T2='2012-12-30 18:30:21.542'IF (DATEDIFF(hh,@T1,@T2) > 83 ) BEGIN SET @T1=(DATEADD(hh,DATEDIFF(hh,@T1,@T2)-83+CAST( rand()*9 +1 AS INT ),@T1))
SET @N= DATEPART(hh,@T1) IF (@N<7) SET @T1=DATEADD(hh,8,@T1) IF (@N>18) SET @t1=DATEADD(hh,-5, @t1)
DECLARE @T2 DATETIME
SET @T1='2012-12-25 00:00:00.000'
SET @T2='2012-12-30 18:30:21.542'
declare @i int
select @i =Datediff(HOUR ,@T1,@T2)
if(@i>83)
begin
set @T1 = DATEADD(HOUR,left(RAND()*18,2),@T1)
print @T1
end
DECLARE @T1 DATETIME
DECLARE @T2 DATETIME
SET @T1='2012-12-25 00:00:00.000'
SET @T2='2012-12-30 18:30:21.542'
declare @i int
select @i =Datediff(HOUR ,@T1,@T2)
if(@i>83)
begin
set @T1 = DATEADD(HOUR, 7+cast(left(RAND()*11,1) as int),@T1)
print @T1
end
--不大明白啊,那你把@t1改成在@t2的基础上加减三天的一个随机数,再处理小时问题
DECLARE @T1 DATETIME
DECLARE @T2 DATETIME
SET @T1='2012-12-25 00:00:00.000'
SET @T2='2012-12-30 18:30:21.542'
declare @i int
select @i =Datediff(HOUR ,@T1,@T2)
if(@i>83)
begin
set @T1 = DATEADD(DAY, -cast(left(4*RAND(),1) as int),convert(varchar(20),@T2,23))
set @T1 = DATEADD(HOUR, 7+cast(left(RAND()*11,1) as int),@T1)
select @T1
select Datediff(HOUR ,@T1,@T2)
end
--比如:
DECLARE @T1 DATETIME
DECLARE @T2 DATETIME
SET @T1='2012-12-25 00:00:00.000'
SET @T2='2012-12-30 18:30:21.542'DECLARE @T3 DateTime
SET @T3='2012-12-30 15:30:21.542' if DATEDIFF(HOUR,@T1,@T2)>83 and DATEPART(HOUR,@T3)>7 and DATEPART(HOUR,@T3)<18
begin
select @T1
set @T1='2012-12-25 13:00:00.000'
endselect @T1
DECLARE @T1 DATETIME
DECLARE @T2 DATETIME
DECLARE @N INT
SET @T1='2012-12-25 00:00:00.000'
SET @T2='2012-12-30 18:30:21.542'IF (DATEDIFF(hh,@T1,@T2) > 83 )
BEGIN
SET @T1=(DATEADD(hh,DATEDIFF(hh,@T1,@T2)-83+CAST( rand()*9 +1 AS INT ),@T1))
SET @N= DATEPART(hh,@T1)
IF (@N<7)
SET @T1=DATEADD(hh,8,@T1)
IF (@N>18)
SET @t1=DATEADD(hh,-5, @t1)
PRINT @T1
END