欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SQL怎么判断两个时间段是否有交叉?

程序员文章站 2022-06-17 21:19:55
sql 代码: if exists (select * from sys.objects where object_id = object_id(n'[dbo].[fun_gettim...

sql 代码:

if  exists (select * from sys.objects where object_id = object_id(n'[dbo].[fun_gettimeslotdays]'))
drop function [dbo].fun_gettimeslotdays
go

-- =============================================
-- author:        
-- create date: <2012-07-18>
-- description:    <判断两个时间断是否有交叉,如果有则返回1,否则返回0>
-- =============================================
create function dbo.fun_gettimeslotdays(
@fromdate datetime,
@todate datetime,
@startdate datetime,
@enddate datetime
)
returns int 
as 
begin
   declare @ret int;
   if(datediff(day,@fromdate,@enddate)>=0 and datediff(day,@enddate,@todate)>=0 )
      set @ret=1;
   if(datediff(day,@startdate,@todate)>=0 and datediff(day,@todate,@enddate)>=0 )
      set @ret=1;
   if (@ret is null)
      set @ret=0;
   return @ret;
end
go

测试代码:

select dbo.fun_gettimeslotdays('2012-03-01','2012-03-10','2012-02-10','2012-02-20');
select dbo.fun_gettimeslotdays('2012-03-01','2012-03-10','2012-02-01','2012-03-01');
select dbo.fun_gettimeslotdays('2012-03-01','2012-03-10','2012-03-01','2012-03-02');
select dbo.fun_gettimeslotdays('2012-03-01','2012-03-10','2012-03-10','2012-03-11');
select dbo.fun_gettimeslotdays('2012-03-01','2012-03-10','2012-03-11','2012-03-11');