How to Determine the Number of Hours Between Two Times in Oracle SQL

Written by sara wayne
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Determine the Number of Hours Between Two Times in Oracle SQL
Oracle SQL can determine the number of hours between two times. (hour image by Mykola Velychko from Fotolia.com)

Structured query language (SQL) is the most widely used method to retrieve and manipulate data stored in an Oracle database. In addition to other powerful commands used to process date values, Oracle SQL provides a method to determine the difference between two points in time. Oracle does not have separate formats for dates and times, but uses a single date format that consists of both the date and time in one column.

Skill level:
Moderate

Other People Are Reading

Things you need

  • Oracle database
  • Interactive SQL tool

Show MoreHide

Instructions

  1. 1

    Find two date columns in an Oracle database. The example uses a static date and the sysdate (system date). Assume that the current system date is January 1, 2010 at 12:00 p.m. Dual is a built-in Oracle system relation that is useful for testing queries that are not dependent on a specific table. Use this command to display the two date values.

    select

    to_char('01/01/2010', 'DD-MM-YYYY HH24:MI:SS') as "Earlier Time",

    to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS') as "Current Time"

    from dual;

    Earlier Time | Current Time

    ------------------------------ | -----------------------------

    01/01/2010 00:00:00 | 01/01/2010 12:00:00

  2. 2

    Change the query to subtract the current time value from the earlier time value. The result is the number of days between the two date values. The portion to the left of the decimal represents days, and the portion to the right represents any fractional portion of a day.

    select

    to_char('01/01/2010', 'DD-MM-YYYY HH24:MI:SS') -

    to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS') as "difference"

    from dual;

    difference


    0.25

  3. 3

    Change the query to multiply the result by 24 to convert the fractional value to hours.

    select

    (to_char('01/01/2010', 'DD-MM-YYYY HH24:MI:SS') -

    to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS') * 24 as "hours"

    from dual;

    hours


    6

Tips and warnings

  • Use the to_char function to vary the format of date fields.
  • Use the same procedure to determine the number of weeks between two dates by changing the "multiply by 24" to "divide by 7."
  • Do not add, multiply or divide date values.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.