Problems with Dates

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
  

Poston Thu, 27 Apr 2006 9:30 pm

Guys, I need to test whether a period 1 is inside a 2 period! I tried using the ' > = < = ' and between, but always fails at some point.

Ex: Period 1: 3/8/2006 the 6/30/2006: 4/1/2006 2 the Period 4/30/2006 anyone have any ideas??

Best regards
asarmento
Location: Salvador - BA

Álvaro Sarmento
Analista de Sistemas
Unitech - Tecnologia de Informação
www.unitech.com.br

Poston Fri, 28 Apr 2006 8:59 am

CREATE OR REPLACE FUNCTION INTERSECAO (VDATA1 IN DATE,--beginning of the period you want to validate VDATA2 IN DATE,--that period ends if you want to validate VDATA3 IN DATE,--beginning of the VDATA4 range IN DATE,--End of range PDATAHORA IN VARCHAR2 DEFAULT ' N ') RETURN VARCHAR2 IS VDATAAUX DATE;
VDATAaux2 DATE;
VDATAFIM DATE;

BEGIN IF VDATA2 IS NULL THEN if PDATAHORA = 'n' then VDATAaux2: = TO_DATE (' 12/31/2999 ', ' DD/MM/YYYY ');
else VDATAaux2: = TO_DATE (' 12/31/2999 23:59 ', ' DD/MM/YYYY HH24: MI ');
end if;
else VDATAaux2: = VDATA2;
END IF;

IF VDATA4 IS NULL THEN if PDATAHORA = 'n' then VDATAFIM: = TO_DATE (' 12/31/2999 ', ' DD/MM/YYYY ');
else VDATAFIM: = TO_DATE (' 12/31/2999 23:59 ', ' DD/MM/YYYY HH24: MI ');
end if;
ELSE VDATAFIM: = VDATA4;
END IF;

if (vdata3 < = vdataaux2 and (vdata4 > = vdata1 > = vdata4 or vdata1 and vdata4 < = vdataaux2 or vdata4 is null)) or (vdata3 > vdata1 and vdata4 is null and vdata2 is null) then RETURN ' TRUE ';
else RETURN ' FALSE ';
end if;

END;
mello
Location: sp

Poston Fri, 28 Apr 2006 9:31 am

Dear Manoj, thanks for the function!

I'll test it immediately!!

Cheers
asarmento
Location: Salvador - BA

Álvaro Sarmento
Analista de Sistemas
Unitech - Tecnologia de Informação
www.unitech.com.br



Return to PL/SQL

Who is online

Users browsing this forum: Google [Bot] and 4 guests

cron