pears.numtotime

Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.

Original SQL

CREATE FUNCTION "pears"."numtotime"( IN "thenum" DECIMAL(5,2) ) 
RETURNS TIME
BEGIN
  DECLARE "rv" TIME;
  DECLARE "ss" CHAR(5);
  DECLARE "ii" SMALLINT;
  DECLARE "num1" DECIMAL(5,2);
  DECLARE "num2" DECIMAL(5,2);
  SET "num1" = "floor"("thenum");
  SET "num2" = "thenum"-"num1";
  IF "thenum" >= 24 OR "thenum" < 0 THEN
    SET "num1" = 23;
    SET "num2" = .59
  END IF;
  IF "num2" > .59 THEN
    SET "num2" = .59
  END IF;
  SET "thenum" = "num1"+"num2";
  SET "ss" = "thenum";
  SET "ii" = "locate"("ss",'.');
  IF "ii" = 0 THEN
    SET "ss" = "ss"+':00'
  ELSE
    IF "ii" = 1 THEN
      SET "ss" = '0' || "ss";
      SET "ii" = 2
    END IF;
    SET "ss" = "left"("ss","ii"-1)+':'+"substr"("ss","ii"+1,2)
  END IF;
  SET "rv" = "ss";
  RETURN("rv")
END