pears.NetShiftDateTime [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.NetShiftDateTime ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
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*;
*/
}
</code>