I have two fields in my table which are start_date & end_date, both of which are of type date and store date in the format mm/dd/yyyy hh:mm:ss.
I would like to know how can I calculate time differences between these two dates. If the two dates are same but time is different ...how could I get time difference using an SQL query in Oracle?
The format for dates is just used for displaying and inserting data. It is not actually stored in that format. It is stored in Oracle's DATE format.
To display the difference between two date fields, just subtract the two columns!! It's that simple. But you'll want to display in a format that shows you the entire result. For example, look at the following query:
SELECT TO_CHAR(end_date-start_date,'MM/DD/YY HH24:MI:SS') AS date_difference FROM table_name;Subtracting the two columns gave us the difference. Converting to a format we can understand completed the step.
For More Information
- Dozens more answers to tough Oracle questions from Brian Peasland are available here.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.