SQL 判断两个时间段是否有交叉
2012-07-18 11:19 Mike.Jiang 阅读(6035) 评论(0) 编辑 收藏 举报费话不说,直接上代码
SQL 代码:
View Code
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fun_GetTimeSlotDays]')) DROP FUNCTION [dbo].fun_GetTimeSlotDays GO -- ============================================= -- Author: <Mike.Jiang> -- 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');
测试结果: