Skip to content

Lesson 10 ยท 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