CODEWITHSUNDEEP
sqloracle-databaseoracle11g
J. Castro
J. Castro

Reputation: 161

Subtraction of dates in Oracle

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.FECHA, 'DD/MM/YYYY') AS FECHA, 
      TO_DATE(SYSDATE, 'DD/MM/YYYY') - TO_DATE(T.FECHA, 'DD/MM/YYYY') AS RESTA 
FROM table t
WHERE T.FECHA IS NOT NULL

Which I throw the following results:

enter image description here

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

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

Which I throw the following results:

enter image description here

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

As you notice the field T.FECHA isVARCHAR2.

Upvotes: 2

Views: 2931

Answers (1)

Littlefoot
Littlefoot

Reputation: 142713

You seem to be part of a rather large group of users who choose to store date values into VARCHAR2 columns; as usual, that's a bad idea - change it, if possible.

Here's an example which show what you should have done:

SQL> create table hp_transact_res_Eval_hipo (fechaeval varchar2(20));

Table created.

SQL> insert into hp_transact_res_Eval_hipo
  2    select '13/05/2009' from dual union
  3    select '14/05/2009' from dual union
  4    select '08/05/2009' from dual union
  5    select '23/07/2010' from dual union
  6    select '22/09/2010' from dual;

5 rows created.
  • apply TO_CHAR to SYSDATE (which is a function that returns the DATE datatype value) with appropriate format mask to display it as dd/mm/yyyy
  • select FECHAEVAL as it is, as you've already stored it as dd/mm/yyyy, so - no use in converting it to date (with TO_DATE)
  • as subtracting two date values produces number of days between those two values, I applied the TRUNC function to SYSDATE in order to remove time component from it, and TO_DATE to FECHAEVAL to convert it to date.
SQL> select
  2    to_char(sysdate, 'dd/mm/yyyy') as hoy,
  3    fechaeval as fecha,
  4    trunc(sysdate) - to_date(fechaeval, 'dd/mm/yyyy') as resta
  5  from hp_transact_res_Eval_hipo;

HOY        FECHA                     RESTA
---------- -------------------- ----------
28/04/2018 08/05/2009                 3277
28/04/2018 13/05/2009                 3272
28/04/2018 14/05/2009                 3271
28/04/2018 22/09/2010                 2775
28/04/2018 23/07/2010                 2836

SQL>

Finally, to show you why you shouldn't store date values into VARCHAR2 columns: this is a valid string (and can be inserted into the table), but invalid date. Subtraction - as expected - results in an error:

SQL> insert into hp_transact_res_eval_hipo values ('33/44/5555');

1 row created.

SQL> select trunc(sysdate) - to_date(fechaeval, 'dd/mm/yyyy') as resta
  2  from hp_transact_res_eval_hipo;
ERROR:
ORA-01847: day of month must be between 1 and last day of month



no rows selected

SQL>

You'll have huge problems if someone enters a value similar to mine. Once again: switch to DATE datatype column.

Upvotes: 1

Related Questions