| date check confusion [message #371567] |
Wed, 08 November 2000 09:16  |
lhj
Messages: 5
Registered: November 2000
|
Junior Member |
|
|
the first tree values in bla shut be the same as in tildato, but the IF-statement ignores my datecheck -
cursor c1 returns
TYPENUMMER VOGNLISTET FRADATO TILDATO
--------------------------------
5254 01.01.1996 01.01.1900
cursor c2 returns
TYPENUMMER VOGNLISTET FRADATO TILDATO
---------- ---------- -------------------- --------------------
5254 01.11.1995 01.12.1996
5254 01.11.1995 01.12.1996
5254 01.11.1995 01.05.1997
5254 01.05.1997 01.01.1900
5254 01.01.1997 01.01.1900
5254 01.01.1997 01.01.1900
5254 01.04.1997 01.01.1900
5254 01.04.1997 01.01.1900
5254 01.01.1996 01.01.1900
After update:
TYPENUMMER VOGNLISTET FRADATO TILDATO ITEMNUMBER BLA
---------- ---------- -------------------- -------------------- -------------------- --------------------
5254 01.11.1995 01.12.1996 CA1079IR 01.01.1900
5254 01.11.1995 01.12.1996 CA848IR 01.01.1900
5254 01.11.1995 01.05.1997 CA1036IR 01.01.1900
5254 01.05.1997 01.01.1900 CA1309IR 01.01.1900
5254 01.01.1997 01.01.1900 CA1306IR 01.01.1900
5254 01.01.1997 01.01.1900 CA1307IR 01.01.1900
5254 01.04.1997 01.01.1900 CA1303IR 01.01.1900
5254 01.04.1997 01.01.1900 CA1322IR 01.01.1900
5254 01.01.1996 01.01.1900 CS684 01.01.1900
My code;
alter session set nls_date_format='DD.MM.YYYY' ;
/
DECLARE
typenr number(32,16);
vogntype varchar2(10);
fra date;
til date;
Ptypenr number(32,16);
Pvogntype varchar2(10);
Pfra date;
Ptil date;
blank_date date;
CURSOR C1 is select typenummer, vognlistetype, fradato, tildato from vognliste where vognlistetype like '_' AND typenummer in (select typenummer from testvogn);
CURSOR C2 is select typenummer, vognlistetype, fradato, tildato from testvogn;
BEGIN
OPEN C1;
OPEN C2;
blank_date:= TO_DATE('01.01.1900', 'DD.MM.YYYY');
LOOP
FETCH C1 into typenr, vogntype, fra, til;
exit when c1%notfound ;
LOOP
FETCH C2 INTO ptypenr, pvogntype, pfra, ptil;
exit when c2%notfound;
IF (ptil = blank_date ) THEN ******this condition check fails *******
update testvogn set bla = til
WHERE typenummer = typenr AND
vognlistetype = vogntype;
commit;
end if;
end loop;
close c2;
end loop;
close c1;
commit;
END;
/
Can anyone solve this datemismatch?
Best Regards
lhj
|
|
|
|
| Re: date check confusion [message #371568 is a reply to message #371567] |
Wed, 08 November 2000 09:50  |
Babu Paul
Messages: 38
Registered: November 2000
|
Member |
|
|
Hi, try using trunc command for the date check....
I think the time components are not matching like. So try using trunc(ptil) = blank_date. This should work hopefully.
Good Luck!
Babu Paul
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
