Subtraction of dates in Oracle

Question

Subtraction of dates in Oracle

Navigation

#1 by (1 votes)

1

Today working with dates in Oracle, perform a subtraction of date with the following query:

SELECT TO_DATE(SYSDATE, 'DD/MM/YYYY') AS HOY, 
      TO_DATE(T.FECHAEVAL, 'DD/MM/YYYY') AS FECHA, 
      TO_DATE(SYSDATE, 'DD/MM/YYYY') - TO_DATE(T.FECHAEVAL, 'DD/MM/YYYY') AS RESTA 
FROM HP_TRANSACT_RES_EVAL_HIPO t
WHERE T.FECHAEVAL IS NOT NULL

Which I throw the following results:

As you can see the subtraction gives me negative results, of course they are not expected, making tests I made the following query:

SELECT TO_DATE(SYSDATE, 'DD/MM/YYYY') AS HOY, 
      TO_DATE(T.FECHAEVAL, 'DD/MM/YYYY') AS FECHA, 
      TO_DATE(SYSDATE) - TO_DATE(T.FECHAEVAL, 'DD/MM/YYYY') AS RESTA 
FROM HP_TRANSACT_RES_EVAL_HIPO t
WHERE T.FECHAEVAL IS NOT NULL

Which I throw the following results:

These are the expected results. Here my question what is the difference between doing the subtraction in the following way TO_DATE(SYSDATE, 'DD/MM/YYYY') - TO_DATE(T.FECHAEVAL, 'DD/MM/YYYY') the way I throw the correct results TO_DATE(SYSDATE) - TO_DATE(T.FECHAEVAL, 'DD/MM/YYYY') ?

As a note, the field T.FECHAEVAL is VARCHAR2 .

oracle-11g

asked by J. Castro 22.03.2018 в 20:26

source

1 answer

How to close with logout? Get messages from properties file in select Spring label

score 1 · Accepted Answer

Apply TO_CHAR to SYSDATE (which is a function that returns the data type value DATE ) with the proper format mask to display it as dd / mm / yyyy .
Select FECHAEVAL as it is, since you have stored it as dd / mm / yyyy , therefore, it is not useful to convert it to date (with TO_DATE )
Since subtracting two date values produces the number of days between those two values, I applied the function TRUNC to SYSDATE to eliminate the time component of it, and TO_DATE to FECHAEVAL to convert it to the date.
```
select to_char(sysdate, 'dd/mm/yyyy') as hoy, 
       fechaeval as fecha,
       trunc(sysdate) - to_date(fechaeval, 'dd/mm/yyyy') as resta 
  from hp_transact_res_Eval_hipo;
```

The result:

Hoy         Fecha       Resta
03/08/2018  13/05/2009  3369
03/08/2018  14/05/2009  3368
03/08/2018  08/05/2009  3374
03/08/2018  23/07/2010  2933
03/08/2018  22/09/2010  2872
03/08/2018  08/05/2009  3374
03/08/2018  28/04/2009  3384