前两天公司有一个功能需求，客户给出几天的工作时间和休息，然后顾客的访问时间必须要在工作时间之内和休息时间之外，所以要求做一下判断。本来以为这个没什么，谁知道客户提供的工作时间段和休息时间段不定，给出的又不是日期格式，是串联后的文本格式，如下格式，想了一下，只能下个函数来解决，分享给其他人看看
GO /****** Object: UserDefinedFunction [dbo].[CheckDate] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ---@day:上班时间段 @dayX:休息时间段 @NowTime:具体的进店时间（分钟为单位） ALTER function [dbo].[CheckDate](@day nvarchar(1000),@dayX nvarchar(1000),@NowTime int) RETURNS varchar(100) as begin declare @start1 int --第一个工作开始时间 declare @start2 int --第二个工作开始时间 declare @start3 int --第三个工作开始时间 declare @end1 int --第一个工作结束时间 declare @end2 int --第二个工作结束时间 declare @end3 int --第三个工作结束时间 set @start1=0 set @start2=0 set @start3=0 set @end1=0 set @end2=0 set @end3=0 declare @Xstart1 int --第一个休息开始时间 declare @Xstart2 int --第二个休息开始时间 declare @Xstart3 int --第三个休息开始时间 declare @Xend1 int --第一个休息结束时间 declare @Xend2 int --第一个休息结束时间 declare @Xend3 int --第一个休息结束时间 set @Xstart1=0 set @Xstart2=0 set @Xstart3=0 set @Xend1=0 set @Xend2=0 set @Xend3=0 declare @jieguo varchar(100) --返回的结果 是:表示正确 否:表示错误 declare @Time nvarchar(1000) --保存分割后的每个时间段 --declare @day nvarchar(1000) --declare @dayX nvarchar(1000) --declare @NowTime int --set @day=‘11:00-13:00$17:00-19:00‘ --set @dayX=‘12:00-14:00‘ --set @NowTime=120 if(len(@day)>0) begin ------------------------------if---------------------------- if(charindex(‘$‘,@day)>0) begin set @Time=left(@day,charindex(‘$‘,@day)-1) set @start1= datepart(hh,left(@Time,charindex(‘-‘,@Time)-1))*60+datepart(n,left(@Time,charindex(‘-‘,@Time)-1)) set @end1= datepart(hh,right(@Time,len(@Time)-charindex(‘-‘,@Time)))*60+datepart(n,right(@Time,len(@Time)-charindex(‘-‘,@Time))) set @day=right(@day,len(@day)-charindex(‘$‘,@day)) --print @Time print @start1 print @end1 print @day end ------------------------------end if---------------------------- ------------------------------else---------------------------- else begin set @Time=@day set @start1= datepart(hh,left(@Time,charindex(‘-‘,@Time)-1))*60+datepart(n,left(@Time,charindex(‘-‘,@Time)-1)) set @end1= datepart(hh,right(@Time,len(@Time)-charindex(‘-‘,@Time)))*60+datepart(n,right(@Time,len(@Time)-charindex(‘-‘,@Time))) set @day=‘‘ --print @Time print @start1 print @end1 print @day end ------------------------------end else ---------------------------- end ------------------------------ end if(len(@day>0))---------------------------- if(len(@day)>0) begin ------------------------------if---------------------------- if(charindex(‘$‘,@day)>0) begin set @Time=left(@day,charindex(‘$‘,@day)-1) set @start2= datepart(hh,left(@Time,charindex(‘-‘,@Time)-1))*60+datepart(n,left(@Time,charindex(‘-‘,@Time)-1)) set @end2= datepart(hh,right(@Time,len(@Time)-charindex(‘-‘,@Time)))*60+datepart(n,right(@Time,len(@Time)-charindex(‘-‘,@Time))) set @day=right(@day,len(@day)-charindex(‘$‘,@day)) --print @Time print @start2 print @end2 print @day end ------------------------------end if---------------------------- ------------------------------else---------------------------- else begin set @Time=@day set @start2= datepart(hh,left(@Time,charindex(‘-‘,@Time)-1))*60+datepart(n,left(@Time,charindex(‘-‘,@Time)-1)) set @end2= datepart(hh,right(@Time,len(@Time)-charindex(‘-‘,@Time)))*60+datepart(n,right(@Time,len(@Time)-charindex(‘-‘,@Time))) set @day=‘‘ --print @Time print @start2 print @end2 print @day end ------------------------------end else ---------------------------- end ------------------------------end if(len(@day>0))---------------------------- if(len(@day)>0) begin ------------------------------if---------------------------- if(charindex(‘$‘,@day)>0) begin set @Time=left(@day,charindex(‘$‘,@day)-1) set @start3= datepart(hh,left(@Time,charindex(‘-‘,@Time)-1))*60+datepart(n,left(@Time,charindex(‘-‘,@Time)-1)) set @end3= datepart(hh,right(@Time,len(@Time)-charindex(‘-‘,@Time)))*60+datepart(n,right(@Time,len(@Time)-charindex(‘-‘,@Time))) set @day=right(@day,len(@day)-charindex(‘$‘,@day)) --print @Time print @start3 print @end3 print @day end ------------------------------end if---------------------------- ------------------------------else---------------------------- else begin set @Time=@day set @start3= datepart(hh,left(@Time,charindex(‘-‘,@Time)-1))*60+datepart(n,left(@Time,charindex(‘-‘,@Time)-1)) set @end3= datepart(hh,right(@Time,len(@Time)-charindex(‘-‘,@Time)))*60+datepart(n,right(@Time,len(@Time)-charindex(‘-‘,@Time))) set @day=‘‘ --print @Time print @start3 print @end3 print @day end ------------------------------end else ---------------------------- end ------------------------------end if(len(@day)>0)---------------------------- if(len(@dayX)>0) begin ------------------------------if---------------------------- if(charindex(‘$‘,@dayX)>0) begin set @Time=left(@dayX,charindex(‘$‘,@dayX)-1) set @Xstart1= datepart(hh,left(@Time,charindex(‘-‘,@Time)-1))*60+datepart(n,left(@Time,charindex(‘-‘,@Time)-1)) set @Xend1= datepart(hh,right(@Time,len(@Time)-charindex(‘-‘,@Time)))*60+datepart(n,right(@Time,len(@Time)-charindex(‘-‘,@Time))) set @dayX=right(@dayX,len(@dayX)-charindex(‘$‘,@dayX)) --print @Time print @Xstart1 print @Xend1 print @dayX end ------------------------------end if---------------------------- ------------------------------else---------------------------- else begin set @Time=@dayX set @Xstart1= datepart(hh,left(@Time,charindex(‘-‘,@Time)-1))*60+datepart(n,left(@Time,charindex(‘-‘,@Time)-1)) set @Xend1= datepart(hh,right(@Time,len(@Time)-charindex(‘-‘,@Time)))*60+datepart(n,right(@Time,len(@Time)-charindex(‘-‘,@Time))) set @dayX=‘‘ --print @Time print @Xstart1 print @Xend1 print @dayX end ------------------------------end else ---------------------------- end ------------------------------ end if(len(@dayX>0))---------------------------- if(len(@dayX)>0) begin ------------------------------if---------------------------- if(charindex(‘$‘,@dayX)>0) begin set @Time=left(@dayX,charindex(‘$‘,@dayX)-1) set @Xstart2= datepart(hh,left(@Time,charindex(‘-‘,@Time)-1))*60+datepart(n,left(@Time,charindex(‘-‘,@Time)-1)) set @Xend2= datepart(hh,right(@Time,len(@Time)-charindex(‘-‘,@Time)))*60+datepart(n,right(@Time,len(@Time)-charindex(‘-‘,@Time))) set @dayX=right(@dayX,len(@dayX)-charindex(‘$‘,@dayX)) --print @Time print @Xstart2 print @Xend2 print @dayX end ------------------------------end if---------------------------- ------------------------------else---------------------------- else begin set @Time=@dayX set @Xstart2= datepart(hh,left(@Time,charindex(‘-‘,@Time)-1))*60+datepart(n,left(@Time,charindex(‘-‘,@Time)-1)) set @Xend2= datepart(hh,right(@Time,len(@Time)-charindex(‘-‘,@Time)))*60+datepart(n,right(@Time,len(@Time)-charindex(‘-‘,@Time))) set @dayX=‘‘ --print @Time print @Xstart2 print @Xend2 print @dayX end ------------------------------end else ---------------------------- end ------------------------------end if(len(@dayX>0))---------------------------- if(len(@dayX)>0) begin ------------------------------if---------------------------- if(charindex(‘$‘,@dayX)>0) begin set @Time=left(@dayX,charindex(‘$‘,@dayX)-1) set @start3= datepart(hh,left(@Time,charindex(‘-‘,@Time)-1))*60+datepart(n,left(@Time,charindex(‘-‘,@Time)-1)) set @Xend3= datepart(hh,right(@Time,len(@Time)-charindex(‘-‘,@Time)))*60+datepart(n,right(@Time,len(@Time)-charindex(‘-‘,@Time))) set @dayX=right(@dayX,len(@dayX)-charindex(‘$‘,@dayX)) --print @Time print @Xstart3 print @Xend3 print @dayX end ------------------------------end if---------------------------- ------------------------------else---------------------------- else begin set @Time=@dayX set @Xstart3= datepart(hh,left(@Time,charindex(‘-‘,@Time)-1))*60+datepart(n,left(@Time,charindex(‘-‘,@Time)-1)) set @Xend3= datepart(hh,right(@Time,len(@Time)-charindex(‘-‘,@Time)))*60+datepart(n,right(@Time,len(@Time)-charindex(‘-‘,@Time))) set @dayX=‘‘ --print @Time print @Xstart3 print @Xend3 print @dayX end ------------------------------end else ---------------------------- end ------------------------------end if(len(@dayX)>0)---------------------------- if((@NowTime>@Xstart1 and @NowTime<@Xend1) or (@NowTime>@Xstart2 and @NowTime<@Xend2) or (@NowTime>@Xstart3 and @NowTime<@Xend3) ) set @jieguo=‘否‘ else if((@NowTime>@start1 and @NowTime<@end1) or (@NowTime>@start2 and @NowTime<@end2) or (@NowTime>@start3 and @NowTime<@end3)) set @jieguo=‘是‘ else set @jieguo=‘否‘ return @jieguo end
自己的IQ比较差，也希望有高手指教另一种思路。