CODEWITHSUNDEEP
sqloracle-database
Haitham Salah
Haitham Salah

Reputation: 107

Oracle - Query the difference between two dates

I am trying to come up with a way in Oracle to calculate the difference between dates in days.

In short. I have two data fields "begins_at" and "ends_at" and the DATA_TYPE for those fields is "TIMESTAMP(6)"

For example. I have the following scenario:

"begins_at" value as “26-MAR-13 02.50.00.000000000 PM”

"ends_at" value as “30-MAR-13 09.25.00.000000000 PM”

I am trying to calculate the different between the two timestamps in days. Is it possible in Oracle?

I tried the following statement but threw an error:

Select TO_DATE(begins_at, 'YYYYMMDD HH:MI:SS AM') - TO_DATE(ends_at, 'YYYYMMDD   HH:MI:SS AM') day_diff

From dual

Any tip is much appreciated.

Upvotes: 1

Views: 31921

Answers (4)

Art
Art

Reputation: 5792

Days, hours, minutes, seconds diff. example - copy/paste to see the output. Replace dates with your dates...:

SELECT start_date, end_date, time_diff,
       EXTRACT(DAY FROM time_diff) days,
       EXTRACT(HOUR FROM time_diff) hours,
       EXTRACT(MINUTE FROM time_diff) minutes,
       EXTRACT(SECOND FROM time_diff) seconds
  FROM
  (
   Select start_date, end_date, (end_date - start_date) time_diff
     From
     (
      Select CAST(to_date('01/01/2012 10:00:00 am', 'dd/mm/yyyy  hh:mi:ss am') AS TIMESTAMP) end_date
           , CAST(to_date('01/01/2012 07:00:00 am', 'dd/mm/yyyy  hh:mi:ss am') AS TIMESTAMP) start_date
       From dual
     )
   )
  /

Subtract time only example:

SELECT trunc(mydate / 3600) hr
     , trunc(mod(mydate, 3600) / 60) mnt
     , trunc(mod(mydate, 3600) / 60 /60) sec
  FROM 
  (
   SELECT (to_date('01/03/2012 10:00:00', 'mm/dd/yyyy  hh24:mi:ss') -
           to_date('01/01/2012 07:00:00', 'mm/dd/yyyy  hh24:mi:ss')) * 86400 mydate
     FROM dual
  )
 /

HR  MNT SEC
-------------
51  0   0

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191570

As our anonymous equine friend noted, 'threw an error' is not helpful. However, it's likely you are getting an ORA-01830, 'date format picture ends before converting entire input string', or ORA-1858, 'a non-numeric character was found where a numeric was expected', or something similar.

The to_date function takes a char argument, so when you pass your timestamp field to it there is an implicit conversion happening; you're essentially doing:

to_date(to_char(begins_at, 'DD-MON-RR'), 'YYYYMMDD HH:MI:SS AM')

... only using your default NLS settings; I'm guessing you're NLS_DATE_FORMAT is set to 'DD-MON-RR' for this example; the exact value will affect the error you get. The format that your timestamp is being given as a string does not match the format you specified for to_date.

You should never rely on implicit conversions, but you don't need to do a conversion here at all. timestamp values can be compared directly, and the results are explained in the documentation. Just subtracting one value form the other will give you the answer as an interval data type:

select ends_at - begins_at from t42;

ENDS_AT-BEGINS_AT
---------------------------------------------------------------------------
+000000004 06:35:00.000000

If you just want the whole day portion you can use extract:

select extract (day from ends_at - begins_at) from t42;

EXTRACT(DAYFROMENDS_AT-BEGINS_AT)
---------------------------------
                                4

You can also treat the timestamp as date using cast:

select cast(ends_at as date) - cast(begins_at as date) from t42;

CAST(ENDS_ATASDATE)-CAST(BEGINS_ATASDATE)
-----------------------------------------
                               4.27430556

I'm not sure why you're using timestamp at all though. The Oracle date type includes a time portion, down to a precision of a second. As you don't (currently) seem to be using the fractional part of the timestamp, sticking to date might make your manipulations simpler, but I guess it depends what you intend to do with these fields later.

Upvotes: 7

Linga
Linga

Reputation: 10573

Try something like this:

SELECT  TO_DATE('20120125 11:00:00 AM', 'YYYYMMDD HH:MI:SS AM')
     - TO_DATE('20120120 10:00:00 AM', 'YYYYMMDD HH:MI:SS AM') day_diff
FROM   dual

You can refer this. It will really help you

Upvotes: 1

Eric Jablow
Eric Jablow

Reputation: 7899

See http://docs.oracle.com/cd/E17952_01/refman-5.1-en/date-and-time-functions.html#function_datediff. It describes DATEDIFF, which returns the difference in days, treating only the date values.

Upvotes: 1

Related Questions