Path: text.usenetserver.com!out02b.usenetserver.com!news.usenetserver.com!in04.usenetserver.com!news.usenetserver.com!in03.usenetserver.com!news.usenetserver.com!news.glorb.com!postnews.google.com!e10g2000prf.googlegroups.com!not-for-mail From: "fitzjarrell@cox.net" Newsgroups: comp.databases.oracle.server Subject: Re: comparing dates Date: Fri, 8 Feb 2008 13:40:10 -0800 (PST) Organization: http://groups.google.com Lines: 115 Message-ID: <1bdfba40-c2ba-4ed7-9467-425924c3e61f@e10g2000prf.googlegroups.com> References: <9ea736b1-5be2-4965-8e3c-6a73c2c69334@e23g2000prf.googlegroups.com> NNTP-Posting-Host: 138.32.32.166 Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Trace: posting.google.com 1202506810 25059 127.0.0.1 (8 Feb 2008 21:40:10 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Fri, 8 Feb 2008 21:40:10 +0000 (UTC) Complaints-To: groups-abuse@google.com Injection-Info: e10g2000prf.googlegroups.com; posting-host=138.32.32.166; posting-account=kBJLegoAAACGAHMa2jhKq7psWYVxkNwe User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1) ; Embedded Web Browser from: http://bsalsa.com/; .NET CLR 1.1.4322; .NET CLR 2.0.50727),gzip(gfe),gzip(gfe) Xref: usenetserver.com comp.databases.oracle.server:441307 X-Received-Date: Fri, 08 Feb 2008 16:40:11 EST (text.usenetserver.com) On Feb 8, 11:37=A0am, Jorge Reyes wrote: > Hi this is an inusual behavior for me, i have the next: > > SELECT =A0(TO_CHAR(vPMM_DATETIME, 'YYYY-MM-DD') || ' 00:00:00') INTO > vINIT_DATE FROM DUAL; > SELECT =A0(TO_CHAR(vPMM_DATETIME, 'YYYY-MM-DD') || ' 00:04:59') INTO > vEND_DATE FROM DUAL; > vFLAG :=3D 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') >=3D > 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') <=3D > 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:=3D1; > END; > END IF; > EXIT WHEN (vFLAG =3D1); > 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)) =A0INTO vEND_DATE FROM > DUAL; > END LOOP; > > so the problem is that the condition cannot be evaluated because i > probe and never set the vFLAG:=3D1; so the loop is infinite, i have > vPMM_DATETIME =3D '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:=3Dsysdate; vFLAG number; vCTR number:=3D1; 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 :=3D 0; LOOP IF (( TO_CHAR(vPMM_DATETIME, 'YYYY-MM-DD HH24:MI:SS') >=3D 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') <=3D 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:=3D1; END; END IF; EXIT WHEN (vFLAG =3D1); dbms_output.put_line(vINIT_DATE||' '||vEND_DATE||' Pass number '||vCTR); vCTR:=3D 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