How to write a sybase sql anywhere function that checks if time of day is between start time and end time

Question

I would like to write a function IsTimeOfDayBetween in Sybase SQL Anywhere that will return true if time is between a start DateTime and an end DateTime and false if it is not.

I am not sure how to go about it - I tried:

DECLARE @isTimeInTimeInterval BIT;
set @isTimeInTimeInterval = (CAST(@startTime as time) > '09:00:00' and CAST(@startTime as time) < '18:00:00');

however it does not work.

Any suggestions would be greatly appreciated.

Answer 1

Here is the answer:

CREATE FUNCTION "DBA"."IsTimeOfDayBetween"(timeOfDay DateTime, startTime      DateTime, endTime DateTime )
  RETURNS BIT
  DETERMINISTIC
  BEGIN
      DECLARE "Result" BIT;
      DECLARE bvrTime TIME;
      DECLARE bvrStartTime TIME;
      DECLARE bvrEndTime TIME;
      SET bvrTime = timeOfDay;
      SET bvrStartTime = startTime;
      SET bvrEndTime= endTime;

      if bvrStartTime = bvrEndTime then
          set "Result" = 1;
      end if;
      if bvrEndTime < bvrStartTime then
            if ((bvrTime <= bvrEndTime) or (bvrTime >= bvrStartTime)) then 
            set "Result" = 1
            else set "Result" = 0
            end if;
       end if;
       if ((bvrTime >= bvrStartTime) and(bvrTime <= bvrEndTime)) then
         set "Result" = 1;
         else set "Result" = 0;
       end if;
      RETURN "Result";
  END

It is based on that, that when assigning a DateTime variable to a Time variable, the last gets only the time part.

Cheers!