How To Use Oracle Date Difference With MS Excel
  Home
 

This topic could be quite challenging for many Oracle database users but the solution is not too complicated at all. Let’s start with the use of The Oracle Date type for calculations and the first example is about subtracting. The output will be returned in number of days and that is suitable for MS Excel. Take a look at the following example:

SELECT (SYSDATE+1) - SYSDATE AS Days
  FROM DUAL;

online tech support computer help computer technician computer problems computer datediff oracle datediff in oracle database programming oracle date oracle date in oracle retirement planning retirement

The subtraction with the dates returned number 1 as we did mention above this is in day units. To show these in Excel just copy the number into a cell on a sheet as on the picture below.

online tech support computer help computer technician computer problems computer datediff oracle datediff in oracle database programming oracle date oracle date in oracle retirement planning retirement

Now doing a right click on the cell and select option “Forma Cells…
online tech support computer help computer technician computer problems computer datediff oracle datediff in oracle database programming oracle date oracle date in oracle retirement planning retirement

From the Format Cells window select the Custom category and since we are dealing with days the option would be something with “dd” and etc. This example option is “dd/mm/yyyy hh:mm“.
online tech support computer help computer technician computer problems computer datediff oracle datediff in oracle database programming oracle date oracle date in oracle retirement planning retirement

The following output “01/01/1900 00:00” does not make much sense because Microsoft Excel replaces missing date values with the Excel default ones. For example the output year 1900 is default and we did not copied it over from Oracle database. To give more sense Orace date difference with Excel we should use time units as on the next example.
online tech support computer help computer technician computer problems computer datediff oracle datediff in oracle database programming oracle date oracle date in oracle retirement planning retirement

The SYSDATE functions can make to return different values with using additional Oracle Interval function. The first date has additional 10 minutes added to the current time as on the example below.

SELECT (SYSDATE + INTERVAL '10' MINUTE) - SYSDATE AS ORACE_DATEDIFF
  FROM DUAL;

online tech support computer help computer technician computer problems computer datediff oracle datediff in oracle database programming oracle date oracle date in oracle retirement planning retirement

The output is showing a 10 minutes difference in day units and the number is 0.006944444444444444444444444444444444444444. To make the number more readable in Excel follow the same steps above as we did with the different days. There is one exception the displaying units should be only time units. The steps with different time units are brought out as following too.

Copy the value into Excel cell as on the following picture.

online tech support computer help computer technician computer problems computer datediff oracle datediff in oracle database programming oracle date oracle date in oracle retirement planning retirement

In the forma cell set the category to Custom and type to “hh:mm:ss“.

online tech support computer help computer technician computer problems computer datediff oracle datediff in oracle database programming oracle date oracle date in oracle retirement planning retirement

On the picture below the 0.006944444444 number makes more sense in time units displaying the 10 minutes difference (00:10:00).

online tech support computer help computer technician computer problems computer datediff oracle datediff in oracle database programming oracle date oracle date in oracle retirement planning retirement

The example above can be used with all Oracle time difference units like seconds, minutes and hours. The MS Excel can represent the numbers more understandable way with just four easy steps.


See Also:
How To Use Oracle Date Format
How To Use Oracle Interval

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)

*