前兩天公司有一個功能需求，客戶給出幾天的工作時間和休息，然后顧客的訪問時間必須要在工作時間之內和休息時間之外，所以要求做一下判斷。本來以為這個沒什么，誰知道客戶提供的工作時間段和休息時間段不定，給出的又不是日期格式，是串聯后的文本格式，如下格式，想了一下，只能下個函數來解決，分享給其他人看看

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='否'