Subtracting a date from SYSDATE
gives negative results:
select floor(SYSDATE - dateemb) from pilote;
FLOOR(SYSDATE-DATEEMB)
----------------------
-29126
-29137
-29522
any solution to this?
table pilote:
Name Null? Type
----------------------------------------- -------- ----------------------------
NUMPIL NOT NULL NUMBER
NOMPIL VARCHAR2(25)
ADRPIL VARCHAR2(25)
SALAIRE NUMBER
COMMISSION NUMBER
DATEEMB DATE
data used:
NUMPIL NOMPIL ADRPIL SALAIRE COMMISSION DATEEMB
------- -------- -------- -------- ---------- ---------
1333 FEDOI NANTES 24000 0 01-MAR-92
6589 DUVAL PARIS 18600 5580 12-MAR-92
7100 MARTIN LYON 15600 16000 01-APR-93
This previous question indicates that there is a date format in Oracle that would make your year 92 into 2092 (i.e in the future) rather than 1992 as probably intended. If this format has been used to insert your data with a 2-digit year then your dates are all wrong, if you write them out in full you should be able to confirm that. The solution would be to fix the data at the point it is created and use 4-digit years.