Discussion of Date and Time function

[Gerri]

Hi All,
Please I want to know how can I Calculate the duration between tow times for Example I've employee Start work 08:30: and end work 05:50.
how can I get difference between two hours for this employee? How many type of calculations can be performed on date and time functions.

[Katty]

If you want to calculate the time duration of two given times values. You can do it but before that your Time would be passed as an argument in the format defined means "5:50 PM" treated as "17:50". Using this method :

(to_date('17:50','HH24:MI') - To_date('08:30','HH24:MI'))*24

Calculated value would be in hour.

[kelfro]

Normally, If you are working on oracle and accessing the date values from database. It follow some rules to insert the value in a proper format and if you goes beyond that format,Oracle restrict you to insert the value. In the case of accessing,there are some defined format used.

You can represent it your own way,suppose you want to access the current date in time stamp format :

SQL>SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') FROM dual;

[Zecho]

Use of MONTHS_BETWEEN Function

This function is used to calculate the duration of month between two dates.This function takes two arguments and return number of month duration.

The syntax :

MONTHS_BETWEEN(<latest_date>, <earliest_date>)

And implementation of syntax would be as follows:

SQL> SELECT MONTHS_BETWEEN(SYSDATE+365, SYSDATE-365) FROM dual;