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

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

 

sql中字符分割,日期判断,以及函数的应用
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
sql中字符分割,日期判断,以及函数的应用

自己的IQ比较差,也希望有高手指教另一种思路。

 

 

sql中字符分割,日期判断,以及函数的应用,布布扣,bubuko.com

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

上一篇:SpringCache整合Redis


下一篇:Apache Cloudstack Development 101 -- Data Access Layer