database programming - Online Tech Support
Home

This online tutorial page is based on examples to be easier to follow. Now about the Oracle Date type; there are many different ways to use the Oracle Date type for calculations and one of them is subtracting. The output below returns the value in number of days as it is in the following example:

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

Doing subtraction with the dates the Oracle datediff will return a number in a day unit. To turn it in hours we need to multiply with 24 hours. As it is written in the next example.

```SELECT ((SYSDATE+1) - SYSDATE) * 24 AS Hours
FROM DUAL;```

To change the result in minutes you will need to multiply the result above with 60 minutes. The following SQL query will return the date diff Oracle in minutes by multiply the result with 60 minutes.

```SELECT ((SYSDATE+1) - SYSDATE) * 24 * 60 AS Minutes
FROM DUAL;```

The fourth Online Tech Support example with Oracle date diff will return the date result in seconds (multiply the result with 60 seconds).

```SELECT ((SYSDATE+1) - SYSDATE) * 24 * 60 * 60 AS Seconds
FROM DUAL;```

This example is how to find the differences in months Online Tech Support will use function MONTHS_BETWEEN that gives us the final result.

```SELECT TRUNC(MONTHS_BETWEEN(SYSDATE,(SYSDATE-60))) AS Months
FROM DUAL;```

The next date calculation is to find amount of years. You would need to convert the number of days in an amount of month and after to convert it to years by dividing it with 12 months. See the example below:

```SELECT TRUNC( MONTHS_BETWEEN(SYSDATE,(SYSDATE-600)) /12 ) AS Years
FROM DUAL;```

The last things is to finding out the date difference in years, months and days together and that will make the query more complicated. See the query below:

```SELECT TRUNC(MONTHS_BETWEEN(SYSDATE,(SYSDATE-600))/12) AS Years,
MOD(TRUNC(MONTHS_BETWEEN(SYSDATE,(SYSDATE-600))),12) AS Months,
SYSDATE - ADD_MONTHS(
(SYSDATE-600),TRUNC(MONTHS_BETWEEN(SYSDATE,(SYSDATE-600)))
) AS Days
FROM DUAL;```

To use more date diff Oracle functions find out below the date format Oracle date masks and using oracle interval functions the date calculating will look more clear and easier to read.

This page is based on examples to be easier to follow. The Oracle NVL2 function returns two different results depending on the first input value. Take a look at the following Oracle NVL2 syntax. Oracle NVL2 returns the second parameter (return_when_NOT_NULL) when the input value (input_value) is NOT empty and otherwise the function returns the third parameter (return_when_NULL).

`NVL2 (<input_value>,<return_when_NOT_NULL>,<return_when_NULL>);`

Please don not get confused with Oracle NVL function that has only two values to compare and it returns first not null value. The following Online Tech Support example shows how the NVL2 function works when the first value is NULL.

```SELECT NVL2(my_value1,my_value2,my_value3)
FROM (SELECT NULL AS my_value1,1 AS my_value2, 2 AS my_value3
FROM dual) ;```

The SQL query returned number “2” as it has been declared as the 3rd value (MY_VALUE3) in the function. The Oracle NVL2 returns the last value in the function when the first input value is empty.

The next Oracle NVL2 example is with the same NVL2 returning values but the first input value is set to 0 (or not null).

```SELECT NVL2(my_value1,my_value2,my_value3)
FROM (SELECT 0 AS my_value1,1 AS my_value2, 2 AS my_value3
FROM dual) ;```

The SQL query output returned value number “1“. As the first input value is NOT empty then function returns the second declared value that is number “1” (MY_VALUE2).

The third example demonstrates that you also can use a text value as for example “Hello“. Oracle NVL2 can be used with most built-in data types and it only checks if the value is NULL or NOT. The example above has first input value not null and the function returns again the second value number “1” as on the last example.

```SELECT NVL2(my_value1,my_value2,my_value3) AS nvl2_result
FROM (SELECT 'Hello' AS my_value1,1 AS my_value2, 2 AS my_value3
FROM dual) ;```

In short: Oracle NVL2 checks if the 1st parameter is null or not and return the 2nd parameter when it is NOT NULL otherwise it returns your 3rd parameter. All parameters are declared by you depending what do you want to be returned.

See Also:
Home Oracle Select Oracle NVL Oracle Decode

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;```

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.

Now doing a right click on the cell and select option “Forma Cells…

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“.

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.

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;```

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.

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

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

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.