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

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