pears.NetShiftDateTime

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetShiftDateTime" IS 
{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*;
*/
}