20140114
20140114
IF  EXISTS (SELECT * FROM dbo.SYSOBJECTS WHERE NAME = 'GetBetweenTime')
DROP FUNCTION [dbo].[GetBetweenTime]
GO

exec dbo.sp_executesql @statement = N'
create function dbo.GetBetweenTime(@CD_STORE varchar(4), @SALE_DATE datetime)
returns varchar(3)
with encryption
as
begin
  declare @CD_CODE    varchar(3)
  declare @SALE_TIME  varchar(5)
  declare @START_TIME varchar(5)
  declare @END_TIME   varchar(5)
  set @SALE_TIME = SubString(Convert(varchar, @SALE_DATE, 8), 1, 5)

  declare cur_time cursor fast_forward
  for
    select   CD_CODE,
             SubString(NM_CODE1, 1, 5) as START_TIME,
             SubString(NM_CODE1, 9, 5) as END_TIME
    from     MS_CODE
    where    CD_STORE = @CD_STORE
      and    CD_KIND  = ''15''
    order by CD_CODE

  open cur_time
  fetch next from cur_time into @CD_CODE, @START_TIME, @END_TIME
  while (@@fetch_status = 0)
  begin
    if (@START_TIME <= @END_TIME) and (@SALE_TIME between @START_TIME and @END_TIME)
      return @CD_CODE
    else if (@START_TIME > @END_TIME) and ((@SALE_TIME between @START_TIME and ''24:00'') or (@SALE_TIME between ''00:00'' and @END_TIME))
      return @CD_CODE
    fetch next from cur_time into @CD_CODE, @START_TIME, @END_TIME
  end
  close cur_time
  deallocate cur_time

  return ''000''
end'
GO