Subtracting a date from SYSDATE in ORACLE

Question

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

Please post the schema of the `pilote` table and the data in the three rows used for the example. — Codo, Jun 03 '12 at 08:00
Your question is strange. Your SQL says `- hire_date`, but your table example says `- DATEEMB`. Can you clarify and show sample data from the table? — Ken White, Jun 03 '12 at 08:05
If hire_date is in the future then your query is correct. If you don't ever want negative numbers then use abs(), but as @Codo said - some data would be a big help. — Dave Richardson, Jun 03 '12 at 08:13
@KenWhite, yes you're right am sorry, it's "dateemb" i was translating from french. — maximz101, Jun 03 '12 at 08:34
Well, that's half of what you were asked for. :-) Now can you supply the data you used to show the results? — Ken White, Jun 03 '12 at 08:38

score 4 · Accepted Answer · edited May 23 '17 at 12:12

4

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.

edited May 23 '17 at 12:12

Community

1
1

answered Jun 03 '12 at 08:51

Dave Richardson

4,880
7
32
47

solution was to re-enter the dates with the full year format. – maximz101 Jun 03 '12 at 09:05

Subtracting a date from SYSDATE in ORACLE

1 Answers1