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:
Other People Are Reading
Things you need
- Oracle database
- Interactive SQL tool
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.
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"
Earlier Time | Current Time
------------------------------ | -----------------------------
01/01/2010 00:00:00 | 01/01/2010 12:00:00
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.
to_char('01/01/2010', 'DD-MM-YYYY HH24:MI:SS') -
to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS') as "difference"
Change the query to multiply the result by 24 to convert the fractional value to hours.
(to_char('01/01/2010', 'DD-MM-YYYY HH24:MI:SS') -
to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS') * 24 as "hours"
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.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for