Re: comparing dates

On Feb 8, 11:37 am, Jorge Reyes <jorg_re..._at_hotmail.com> wrote:
> Hi this is an inusual behavior for me, i have the next:
>
> SELECT  (TO_CHAR(vPMM_DATETIME, 'YYYY-MM-DD') || ' 00:00:00') INTO
> vINIT_DATE FROM DUAL;
> SELECT  (TO_CHAR(vPMM_DATETIME, 'YYYY-MM-DD') || ' 00:04:59') INTO
> vEND_DATE FROM DUAL;
> vFLAG := 0;
> LOOP
> IF (( TO_CHAR(TO_DATE(TO_CHAR(vPMM_DATETIME, 'YYYY-MM-DD
> HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') >=
> TO_CHAR(TO_DATE(TO_CHAR(vINIT_DATE, 'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-
> DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS')) AND
> (TO_CHAR(TO_DATE(TO_CHAR(vPMM_DATETIME, 'YYYY-MM-DD HH24:MI:SS'),'YYYY-
> MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') <=
> TO_CHAR(TO_DATE(TO_CHAR(vEND_DATE, 'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-
> DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS'))) THEN
> BEGIN
> SELECT TO_CHAR(vINIT_DATE, 'YYYY-MM-DD HH24:MI:SS') INTO vRANGO FROM
> dual;
> vFLAG:=1;
> END;
> END IF;
> EXIT WHEN (vFLAG =1);
> SELECT TO_CHAR(vINIT_DATE + (1/24/60/60 *300)) INTO vINIT_DATE FROM
> DUAL;
> SELECT TO_CHAR(vEND_DATE + (1/24/60/60 *299))  INTO vEND_DATE FROM
> DUAL;
> END LOOP;
>
> so the problem is that the condition cannot be evaluated because i
> probe and never set the vFLAG:=1; so the loop is infinite, i have
> vPMM_DATETIME = '2008-01-10 11:20:15' and the goal is that the vRANGO
> must be '2008-01-10 11:20:00', everything is ok but the date compare
> is the problem please help us.
>
> thanks in advance, regards from Mexico...

I cannot get your code snippet to work. Working from your posted code the only type that vINIT_DATE and vEND_DATE can be is a character string, so the conversion from character string to character string fails:

declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 16

Making these variables dates does no better:

ERROR at line 2:
ORA-01861: literal does not match format string ORA-06512: at line 13

So I can't understand HOW you get into an endless loop when the loop never starts. Also, you have your date adjustments backwards; the vINIT_DATE should have the 299 multiplier, and the vEND_DATE the 300 multiplier, to get a valid range with which to work.

I finally was able to get a modification of your code to work, and work properly (meaning it terminates without throwing an error), with 187 iterations through the loop:

declare
vINIT_DATE varchar2(20);
vEND_DATE varchar2(20);
vRANGO varchar2(20);
vPMM_DATETIME date:=sysdate;
vFLAG number;
vCTR number:=1;

begin

	SELECT  (TO_CHAR(vPMM_DATETIME, 'YYYY-MM-DD') || ' 00:00:00') INTO
	vINIT_DATE FROM DUAL;
	SELECT  (TO_CHAR(vPMM_DATETIME, 'YYYY-MM-DD') || ' 00:04:59') INTO
	vEND_DATE FROM DUAL;
	vFLAG := 0;
	LOOP
		IF (( TO_CHAR(vPMM_DATETIME, 'YYYY-MM-DD HH24:MI:SS') >=
		TO_CHAR(TO_DATE(vINIT_DATE, 'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD
HH24:MI:SS')) AND
		(TO_CHAR(vPMM_DATETIME, 'YYYY-MM-DD HH24:MI:SS') <=
		TO_CHAR(TO_DATE(vEND_DATE, 'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD
HH24:MI:SS'))) THEN
		BEGIN
				SELECT TO_CHAR(to_date(vINIT_DATE, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-
MM-DD HH24:MI:SS') INTO vRANGO FROM
				dual;
				vFLAG:=1;
		END;
	END IF;
	EXIT WHEN (vFLAG =1);
	dbms_output.put_line(vINIT_DATE||'   '||vEND_DATE||'    Pass number
'||vCTR);
	vCTR:= vCTR+1;
	SELECT TO_CHAR(to_date(vINIT_DATE,'YYYY-MM-DD HH24:MI:SS') +
(1/24/60/60 *299), 'YYYY-MM-DD HH24:MI:SS') INTO vINIT_DATE FROM DUAL;
	SELECT TO_CHAR(to_date(vEND_DATE, 'YYYY-MM-DD HH24:MI:SS') +
(1/24/60/60 *300), 'YYYY-MM-DD HH24:MI:SS') INTO vEND_DATE FROM DUAL;
	END LOOP;

end;
/

Your code is a mess and I can't understand how it's actually working, if at all. And I don't understand the purpose behind it.

Explain, exactly, what you're doing, what needs to be accomplished and possibly someone can help you further.

David Fitzjarrell Received on Fri Feb 08 2008 - 15:40:10 CST