pears.NetShiftDateTime

CREATE FUNCTION "pears"."NetShiftDateTime"( IN "pDate" DATE,IN "pTimeFrom" TIME,IN "pTimeTo" TIME,IN "pFromOrTo" CHAR(1) ) 
RETURNS TIMESTAMP
deterministic
BEGIN
  DECLARE "rv" TIMESTAMP;
  IF "ptimefrom" IS NULL OR "ptimeto" IS NULL THEN
    IF "pFromOrTo" = 'F' THEN
      SET "rv" = "pdate"
    ELSE
      SET "rv" = "pdate"+CAST('23:59:59' AS TIME)
    END IF
  ELSE IF "pFromOrTo" = 'F' THEN
      SET "rv" = "pdate"+"pTimeFrom"
    ELSE
      IF "pTimeFrom" > "pTimeTo" THEN
        SET "pdate" = "pdate"+1
      END IF;
      SET "rv" = "pdate"+"pTimeTo"
    END IF END IF;
  RETURN "rv"
END /* TEST
select NetShiftDateTime('2012-01-31',null,null,'f') as x;
expect x=31/01/2012;
select NetShiftDateTime('2012-01-31',null,null,'t') as x;
expect x=31/01/2012 23:59*;
select NetShiftDateTime('2012-01-31','09:00','17:30','f') as x;
expect x=31/01/2012 09:00*;
select NetShiftDateTime('2012-01-31','09:00','17:30','t') as x;
expect x=31/01/2012 17:30*;
select NetShiftDateTime('2012-01-31','22:00','06:30','f') as x;
expect x=31/01/2012 22:00*;
select NetShiftDateTime('2012-01-31','22:00','06:30','t') as x;
expect x=01/02/2012 06:30*;
*/