0

How would one subtract dates in a mm/dd/yyyy format so I can return a whole number in days?(exclude time in my columns)

This is what I have so far:

(A.START_DT - A.END_DT) AS DaysHere


  Start Date               End Date
11/6/2012 10:33:25 AM      12/17/2013
Mr John
  • 231
  • 1
  • 3
  • 18
  • What was the issue with `SELECT (A.START_DT - A.END_DT) FROM yourtable` ? – Sameer Mirji Feb 26 '16 at 14:47
  • Possible duplicate of [How to subtract 2 dates in oracle to get the result in hour and minute](http://stackoverflow.com/questions/7460266/how-to-subtract-2-dates-in-oracle-to-get-the-result-in-hour-and-minute) – Sameer Mirji Feb 26 '16 at 14:49
  • @SameerMirji It returns results but I would like to subtract mm/dd/yyyy only and not subtract time. – Mr John Feb 26 '16 at 14:49
  • 1
    `SELECT (to_date(A.START_DT, 'mm/dd/yyyy') - to_date(A.END_DT, 'mm/dd/yyyy')) FROM yourtable A` will do it. – Sameer Mirji Feb 26 '16 at 14:54
  • @SameerMirji I'm getting an error. "A non numeric character was found where a numeric was expected" – Mr John Feb 26 '16 at 15:02
  • @SameerMirji got it to work... (TO_DATE(a.start_Dt, 'DD-MM-YYYY') - TO_DATE(a.end_dt, 'DD-MM-YYYY')) as dayshere – Mr John Feb 26 '16 at 15:20
  • 1
    if `a.start_dt` and `a.end_dt` are `date` columns then use `trunc`: `select trunc(a.start_dt) - trunc(a.end_dt) ...` – Tony Andrews Feb 26 '16 at 15:27

1 Answers1

0

You could use to_date to specify the format.

DB(ADMIN)=> 
  select 
    to_date(start_date, 'MM/DD/YYYY HH:mi:SS AM') 
    - to_date(end_date, 'MM/DD/YYYY') dayshere 
  from so;
 DAYSHERE
----------
     -406
Jeremy Fortune
  • 2,459
  • 1
  • 18
  • 21