--或者用如下函数
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_randtime]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_randtime]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[v_rand]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[v_rand]
GO
--需要这样一个视图
create view v_rand as select re=rand()
go
/*--取得指定上下限的随机时间
按秒生成随机时间
--邹建 2004.08(引用请保留此信息)--*/
/*--调用示例
select dbo.f_randtime('10:30','12:30')
select fiscalno,attenddate,substring(convert(varchar,dbo.f_randtime(dateadd(n,-10,convert(datetime,shifton)),shifton),120),12,5)+'M',
--convert(varchar(10),attenddate,120)+' '+left(shift1_on,5)+'.000',
--convert(varchar(10),attenddate,120)+' '+shifton+'.000',
shift1_on,shifton
from dbo.tb_AnlaysisDetail a inner join dbo.tb_ShiftDetail b
on a.preshift=b.shiftcode
where attenddate between '2009-03-01' and '2009-03-31'
and isnull(shift1_on,'')<>''
and shift1_on<>' M'
and ordertag=1
and datediff(n,convert(datetime,convert(varchar(10),attenddate,120)+' '+left(shift1_on,5)+'.000'),convert(datetime,convert(varchar(10),attenddate,120)+' '+shifton+'.000'))>15
--*/
create function f_randtime(
@begin_date datetime,
@end_date datetime
)
returns datetime
as
begin
return(dateadd(n
,(select re*datediff(n,@begin_date,@end_date) from v_rand)
,@begin_date))
end
go
按秒生成随机时间
最新推荐文章于 2023-09-06 15:31:50 发布