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>
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>