T-SQL在某时间段内生成随机时间

用CHECKSUM()加NEWID()在sql中生成随机时间

CREATE PROCEDURE upc_GetRandDateTime
@beginDate DATETIME,--起始时间
@endDate DATETIME,--结束时间
@beginHMS VARCHAR(14),--起始时间的时分秒
@dateTime DATETIME OUTPUT
AS
BEGIN
    DECLARE @begin BIGINT,@span BIGINT
    SELECT @begin=CAST(@beginDate AS BIGINT),@span=CAST(@endDate-@beginDate AS BIGINT)
    DECLARE @dateTimeTb TABLE(RDDateTime DATETIME)
    DECLARE @i INT 
    SET @i=0
    WHILE @i<100
    BEGIN
        INSERT INTO @dateTimeTb( RDDateTime )
        SELECT CAST(ABS(CHECKSUM(NEWID()))%@span+@begin AS DATETIME)+@beginHMS+RAND()
        SET @i=@i+1
    END
    SELECT TOP 1 @dateTime=RDDateTime FROM @dateTimeTb WHERE DATEPART(HOUR,RDDateTime) BETWEEN 09 AND 18 ORDER BY NEWID()
END
go

 

转载于:https://www.cnblogs.com/DonnyPeng/p/4390278.html