sql中字符分割，日期判断，以及函数的应用

前两天公司有一个功能需求，客户给出几天的工作时间和休息，然后顾客的访问时间必须要在工作时间之内和休息时间之外，所以要求做一下判断。本来以为这个没什么，谁知道客户提供的工作时间段和休息时间段不定，给出的又不是日期格式，是串联后的文本格式，如下格式，想了一下，只能下个函数来解决，分享给其他人看看

GO/****** Object:  UserDefinedFunction [dbo].[CheckDate]   ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO---@day:上班时间段   @dayX:休息时间段   @NowTime:具体的进店时间（分钟为单位）ALTER function [dbo].[CheckDate](@day nvarchar(1000),@dayX nvarchar(1000),@NowTime int)RETURNS varchar(100)asbegindeclare @start1 int   --第一个工作开始时间declare @start2 int   --第二个工作开始时间declare @start3 int   --第三个工作开始时间declare @end1 int     --第一个工作结束时间declare @end2 int     --第二个工作结束时间declare @end3 int     --第三个工作结束时间set @start1=0set @start2=0set @start3=0set @end1=0set @end2=0set @end3=0declare @Xstart1 int    --第一个休息开始时间declare @Xstart2 int    --第二个休息开始时间declare @Xstart3 int    --第三个休息开始时间declare @Xend1 int      --第一个休息结束时间 declare @Xend2 int        --第一个休息结束时间 declare @Xend3 int        --第一个休息结束时间 set @Xstart1=0set @Xstart2=0set @Xstart3=0set @Xend1=0set @Xend2=0set @Xend3=0declare @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=120if(len(@day)>0)begin------------------------------if----------------------------if(charindex('$',@day)>0)beginset @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 @dayend------------------------------end if----------------------------------------------------------else----------------------------elsebeginset @Time=@dayset @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 @dayend------------------------------end else ----------------------------end------------------------------ end if(len(@day>0))----------------------------if(len(@day)>0)begin------------------------------if----------------------------if(charindex('$',@day)>0)beginset @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 @dayend------------------------------end if----------------------------------------------------------else----------------------------elsebeginset @Time=@dayset @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 @dayend------------------------------end else ----------------------------end ------------------------------end if(len(@day>0))----------------------------if(len(@day)>0)begin------------------------------if----------------------------if(charindex('$',@day)>0)beginset @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 @dayend------------------------------end if----------------------------------------------------------else----------------------------elsebeginset @Time=@dayset @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 @dayend------------------------------end else ----------------------------end ------------------------------end if(len(@day)>0)----------------------------if(len(@dayX)>0)begin------------------------------if----------------------------if(charindex('$',@dayX)>0)beginset @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 @dayXend------------------------------end if----------------------------------------------------------else----------------------------elsebeginset @Time=@dayXset @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 @dayXend------------------------------end else ----------------------------end------------------------------ end if(len(@dayX>0))----------------------------if(len(@dayX)>0)begin------------------------------if----------------------------if(charindex('$',@dayX)>0)beginset @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 @dayXend------------------------------end if----------------------------------------------------------else----------------------------elsebeginset @Time=@dayXset @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 @dayXend------------------------------end else ----------------------------end ------------------------------end if(len(@dayX>0))----------------------------if(len(@dayX)>0)begin------------------------------if----------------------------if(charindex('$',@dayX)>0)beginset @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 @dayXend------------------------------end if----------------------------------------------------------else----------------------------elsebeginset @Time=@dayXset @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 @dayXend------------------------------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='否'