Skip to content
Tutorials Center
My learning

Topics

Lesson 10 of 38 ยท Functions and Expressions

Number and Date Functions in Oracle

Round and truncate numbers, use MOD and POWER, and work with Oracle dates: SYSDATE, date arithmetic, ADD_MONTHS, MONTHS_BETWEEN, LAST_DAY, TRUNC and EXTRACT.

Numbers and dates drive reports: totals rounded to cents, orders per month, accounts older than ninety days. Oracle has a neat design here. Subtracting two dates gives a number of days, adding a number to a date moves it forward, and a handful of functions deal with the awkward parts (month ends, truncating times, rounding to the nearest week).

Oracle-only calls are read-only code with the result stated. Runnable stand-ins use SQLite's date() and julianday(), which compute the same values.

Numeric functions

Function Meaning Example and result
ROUND(n, d) Round to d decimals ROUND(15.678, 1) is 15.7; ROUND(1250, -2) is 1300
TRUNC(n, d) Cut off decimals without rounding TRUNC(15.678, 1) is 15.6
CEIL(n) / FLOOR(n) Next integer up / down CEIL(4.1) is 5; FLOOR(-4.1) is -5
MOD(a, b) Remainder (sign follows a) MOD(17, 5) is 2
REMAINDER(a, b) IEEE remainder (nearest quotient) REMAINDER(17, 5) is 2; REMAINDER(18, 5) is -2
ABS(n), SIGN(n) Absolute value, -1/0/1
POWER(a, b), SQRT(n) Exponent, square root POWER(2, 10) is 1024
GREATEST(a, b, ...), LEAST(...) Largest / smallest of the arguments GREATEST(3, 9, 5) is 9

ROUND rounds halves away from zero: ROUND(2.5) is 3 and ROUND(-2.5) is -3. MOD(a, 0) returns a, not an error.

SQL Rounding and remainders ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

(Oracle writes MOD(stock, 3); SQLite uses the % operator. TRUNC(price) becomes cast(price AS INTEGER) for positive values.)

Getting the current date and time

SQL
SELECT SYSDATE, CURRENT_DATE, SYSTIMESTAMP, CURRENT_TIMESTAMP FROM dual;
Name Type Time zone
SYSDATE DATE The database server's clock
CURRENT_DATE DATE Your session's time zone
SYSTIMESTAMP TIMESTAMP WITH TIME ZONE The server, with fractions of a second
CURRENT_TIMESTAMP TIMESTAMP WITH TIME ZONE Your session

These values contain a time of day, even when SQL*Plus shows only the date. To display it, convert with TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'), covered in conversion functions.

Date arithmetic

In Oracle, a DATE plus a number adds days, and the difference of two dates is a number of days (with fractions for the time part):

SQL
SYSDATE + 7                     -- one week from now
SYSDATE - 1/24                  -- one hour ago
SYSDATE + 30/1440               -- 30 minutes from now
hire_date - SYSDATE             -- days until a date (negative if in the past)
TRUNC(SYSDATE) - TRUNC(hire_date)   -- whole days between two calendar dates

Intervals read nicely: SYSDATE + INTERVAL '2' HOUR or DATE '2024-01-31' + INTERVAL '1' MONTH. Month intervals fail on a date that does not exist in the target month (ORA-01839), so use ADD_MONTHS for month math.

Stand-in with SQLite's date modifiers and julianday:

SQL Date arithmetic (stand-in) ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

ADD_MONTHS, MONTHS_BETWEEN, LAST_DAY, NEXT_DAY

SQL
ADD_MONTHS(DATE '2024-01-31', 1)           -- 2024-02-29  (clamped to month end)
ADD_MONTHS(DATE '2024-03-15', -6)          -- 2023-09-15
MONTHS_BETWEEN(DATE '2024-06-15', DATE '2024-01-15')   -- 5
LAST_DAY(DATE '2024-02-10')                -- 2024-02-29
NEXT_DAY(DATE '2024-05-15', 'MONDAY')      -- 2024-05-20  (the next Monday after the date)

ADD_MONTHS keeps the end-of-month property: ADD_MONTHS(DATE '2024-02-29', 12) gives 2025-02-28. MONTHS_BETWEEN returns a fraction when the days do not line up (it counts 31-day months). The day names for NEXT_DAY follow your session language.

The runnable stand-in for ADD_MONTHS/LAST_DAY uses date modifiers:

SQL Month math (stand-in) ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

TRUNC and ROUND on dates

Because every DATE carries a time, TRUNC is the everyday way to strip it. TRUNC(date, 'unit') rounds down, ROUND(date, 'unit') to the nearest:

Call Result for 2024-05-17 15:45:00
TRUNC(d) 2024-05-17 00:00:00
TRUNC(d, 'MM') 2024-05-01 (first of the month)
TRUNC(d, 'YYYY') 2024-01-01
TRUNC(d, 'IW') 2024-05-13 (Monday of that ISO week)
TRUNC(d, 'Q') 2024-04-01 (start of the quarter)
ROUND(d) 2024-05-18 (afternoon rounds up)

Truncating is how to count orders per day or per month:

SQL
SELECT TRUNC(ordered_on, 'MM') AS month, COUNT(*) AS orders
FROM orders
GROUP BY TRUNC(ordered_on, 'MM')
ORDER BY month;

To find "everything that happened today" use a half-open range, which can use an index, instead of wrapping the column in TRUNC:

SQL
WHERE ordered_on >= TRUNC(SYSDATE) AND ordered_on < TRUNC(SYSDATE) + 1

Runnable stand-in of the monthly count:

SQL Orders per month (stand-in for TRUNC(date, 'MM')) ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

EXTRACT

EXTRACT(part FROM date) pulls out one component: YEAR, MONTH, DAY, and for timestamps HOUR, MINUTE, SECOND, TIMEZONE_HOUR. On a plain DATE, only year, month and day work; use TO_CHAR(d, 'HH24') for the hour.

SQL
SELECT EXTRACT(YEAR FROM hire_date) AS yr, COUNT(*) FROM employees GROUP BY EXTRACT(YEAR FROM hire_date);

Stand-in: CAST(strftime('%Y', hire_date) AS INTEGER).

SQL Hires per year (stand-in for EXTRACT) ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is SYSDATE + 1?

    Adding a number to an Oracle DATE adds that many days. Use fractions for hours and minutes (1/24, 1/1440).
  2. 2. What does TRUNC(SYSDATE) return?

    TRUNC on a date removes the time portion, returning midnight of the same day.
  3. 3. Which function finds the last day of the month containing a date?

    LAST_DAY(d) returns the final day of that month. TRUNC(d, 'MM') gives the first day.
  4. 4. What is ROUND(-2.5)?

    Oracle rounds halves away from zero, so -2.5 becomes -3.

Summary

  • ROUND, TRUNC, CEIL, FLOOR and MOD cover most numeric work; ROUND rounds halves away from zero.
  • DATE + n adds days, and the difference of two dates is a number of days.
  • ADD_MONTHS, MONTHS_BETWEEN, LAST_DAY and NEXT_DAY handle calendar quirks.
  • TRUNC(date, 'MM') and friends group dates by month, year, week or quarter.
  • EXTRACT reads a single component of a date or timestamp.

Next: Conversion and NULL Functions