Learning Objectives – after this chapter you will be able to:
- Explain what a function is and classify Oracle functions; distinguish single-row and group functions.
- Use arithmetic functions – ABS, CEIL, FLOOR, MOD, POWER, ROUND, TRUNC, SQRT, SIGN and scientific functions.
- Use the DUAL table.
- Perform date arithmetic and use date functions – ADD_MONTHS, MONTHS_BETWEEN, LAST_DAY, NEXT_DAY, NEW_TIME, ROUND and TRUNC.
What is a Function?
A function is a name that performs a specific task. It may or may not take values (arguments), but it always returns a value. Arguments are written in parentheses after the name; a function without arguments (e.g. SYSDATE) is used without parentheses.
| Type | Works on |
|---|---|
| Arithmetic | Numeric data |
| Date & time | DATE data |
| String | Strings (Chapter 7) |
| Conversion | Convert a value from one type to another (Chapter 7) |
| Miscellaneous | Any type of data (Chapter 7) |
| Group (aggregate) | Groups of rows created by GROUP BY (Chapter 8) |
Group (aggregate) functions return one result per group of rows; all other functions are single-row functions – they return a result for each row.
Arithmetic Functions
| Function | Returns | Example → Result |
|---|---|---|
| ABS(value) | Absolute value | ABS(-5) → 5 |
| CEIL(value) | Smallest integer ≥ value | CEIL(1.3) → 2, CEIL(-2.3) → -2 |
| FLOOR(value) | Largest integer ≤ value | FLOOR(1.3) → 1, FLOOR(-2.3) → -3 |
| MOD(value, divisor) | Remainder of the division | MOD(10,4) → 2 |
| POWER(value, exponent) | value raised to exponent | POWER(2,3) → 8 |
| ROUND(value [,precision]) | Rounded value | ROUND(1047.785,2) → 1047.79 |
| TRUNC(value [,precision]) | Truncated value (no rounding) | TRUNC(1047.785,2) → 1047.78 |
| SQRT(value) | Square root | SQRT(25) → 5 |
| SIGN(value) | 1 if > 0, -1 if < 0, 0 if = 0 | SIGN(-7) → -1 |
ROUND and TRUNC with positive and negative precision
A positive precision works on digits to the right of the decimal point; a negative precision works to the left. ROUND adds one to the last retained digit if the leftmost digit being lost is ≥ 5; TRUNC simply drops the digits.
| Expression | Result |
|---|---|
| ROUND(1295.356, 2) | 1295.36 |
| TRUNC(1295.356, 2) | 1295.35 |
| ROUND(1295.356, 0) | 1295 |
| ROUND(1285.356, -1) | 1290 |
| TRUNC(1285.356, -1) | 1280 |
| ROUND(1295, -2) | 1300 |
Scientific functions (rare in business applications): ACOS, ASIN, ATAN, COS, COSH, EXP (e raised to a power, e.g. EXP(2) = 7.3890561), LN, LOG(base, value), SIN, SINH, TAN, TANH. Trigonometric functions work in radians.
The DUAL Table
DUAL is available to every account and contains one row and one column (DUMMY VARCHAR2(1)). Use it with SELECT to display the result of an expression once, when the expression is not related to any table.
Using DUAL and ROUND with a table
select sysdate from dual; select mod(10,4) from dual; select ccode, name, round(fee,-3) "fee" from courses; -- fee rounded to thousands
Date Arithmetic
- date + number → the date that many days later.
- date − number → the date that many days earlier.
- date − date → number of days between them (the fraction is the time difference).
Date arithmetic
select name, sysdate - dj from students; -- days since joining select name, dj, dj + 10 "Due Date" from students; -- instalment due in 10 days select * from payments where sysdate - dp <= 3; -- payments of the last 3 days
A DATE always stores date and time. If no time is given it is 00:00:00 (12:00 a.m.). The default format DD-MON-YY does not show the time, but all comparisons include the time portion.
Date Functions
All date functions return a DATE, except MONTHS_BETWEEN, which returns a number.
| Function | Description |
|---|---|
| ADD_MONTHS(date, count) | Adds (or, with a negative count, subtracts) months. If the target month lacks the day, the last day is used: ADD_MONTHS('31-aug-2001',1) → 30-SEP-01. |
| MONTHS_BETWEEN(date1, date2) | Months between two dates; the fraction is calculated on a 31-day month and includes time (12-JAN-01 to 20-FEB-01 → 1.258). Wrap in TRUNC to ignore it. |
| LAST_DAY(date) | Last day of the month of the date. |
| NEXT_DAY(date, 'day') | Date of the next given weekday after the date – always greater than the date. Use NEXT_DAY(date − 1, 'Sat') to get the same date when it already is a Saturday. |
| NEW_TIME(date, 'this', 'other') | Converts a time from one time zone to another (GMT, EST/EDT, PST/PDT, AST/ADT, HST/HDT …). |
| ROUND(date) | Next day if the time is at or after 12 noon; time set to 00:00:00. |
| TRUNC(date) | Same date, time set to 00:00:00 (never increments). |
Practical date queries on the sample tables
select bcode, ccode, enddate, add_months(enddate,3) "Cert. Date" from batches where enddate is not null; -- certificates 3 months after end select bcode, trunc(months_between(enddate, stdate)) "NO. Months" from batches where enddate is not null; select bcode, stdate, last_day(stdate) + 5 "Due Date" from batches; -- 5th of next month select * from payments where dp = sysdate; -- no rows: times differ select * from payments where trunc(dp) = trunc(sysdate); -- correct
Key Takeaways
- ROUND rounds, TRUNC cuts; negative precision works left of the decimal point.
- DUAL is a one-row table for evaluating expressions.
- DATE includes time – use TRUNC(date) when comparing only the date.
- MONTHS_BETWEEN returns a number; other date functions return dates.
Chapter 6 Notes (PDF)
ch6.pdfKeys: ← → page · + − zoom · F full screen
Question / Answer (15 questions)
Moderate-level questions based on the Chapter 6 notes (PDF). Click a question to see the answer.
Answer : A function is a name that performs a specific task. It may or may not take values (arguments), given in parentheses after the name, but it always returns a value as the result. If it takes no values the name can be used alone without parentheses.
Answer : Arithmetic functions, date and time functions, string functions, conversion functions, miscellaneous functions and group functions.
Answer : Group (aggregate) functions perform their operation on a group – a collection of rows created by GROUP BY – and return one result per group. All the other functions are single-row functions because they return a result for each row.
Answer : ROUND takes the leftmost digit being lost into account and adds one to the rightmost retained digit if it is 5 or more; TRUNC simply drops the digits. For example, ROUND(1047.785,2) gives 1047.79 while TRUNC(1047.785,2) gives 1047.78.
Answer : A negative precision rounds to the left of the decimal point. In ROUND(1285.356,-1) the one digit left of the decimal point (5) is set to zero; since it is 5 or more, one is added to the digit on its left (8 becomes 9), so the result is 1290.
Answer : CEIL returns the smallest integer greater than or equal to the value: CEIL(1.3)=2, CEIL(2)=2, CEIL(-2.3)=-2. FLOOR returns the largest integer less than or equal to the value: FLOOR(1.3)=1, FLOOR(2)=2, FLOOR(-2.3)=-3.
Answer : DUAL is a table available to every account that contains one row and one column (DUMMY VARCHAR2(1)). It is used with SELECT when the result of an expression not related to any table, such as SYSDATE or MOD(10,4), is to be displayed only once.
Answer : Adding a number to a date to get the date after that many days, subtracting a number from a date to get the date before that many days, and subtracting two dates to get the number of days between them.
Answer : Because the Oracle DATE datatype stores both date and time. SYSDATE contains the current time while DJ has time 00:00:00 (when no time is given), so the fraction in the difference represents the difference in time.
Answer : Oracle compares both date and time portions; even if DP has today’s date, its time portion does not match SYSDATE’s time. Truncating both sets the times to 00:00:00 so only dates are compared: WHERE TRUNC(dp) = TRUNC(SYSDATE).
Answer : The last day of the target month is taken. For example, ADD_MONTHS('31-aug-2001',1) returns 30-SEP-01 because 31-SEP does not exist. A negative count subtracts months.
Answer : It returns the number of months between two dates as a number; the fraction represents the days beyond whole months, calculated on a 31-day month and including time. For example, between 12-JAN-01 and 20-FEB-01 it returns about 1.258. TRUNC(MONTHS_BETWEEN(...)) ignores the fraction.
Answer : LAST_DAY(date) returns the last day of the month of the given date. LAST_DAY(stdate) + 5 gives the 5th of the month after the batch started, used as the due date of the first instalment.
Answer : NEXT_DAY(date, 'day') returns the date of the given weekday that is greater than the given date, so it never returns the same date. To get the same date when it already falls on that weekday, subtract one first: NEXT_DAY(date - 1, 'Sat').
Answer : ROUND(date) increments the date by one day if the time is at or after 12 noon, and TRUNC(date) never increments it; both set the time portion to 00:00:00. NEW_TIME(date, 'this', 'other') returns the time in another time zone (e.g. GMT to AST) for the given time.
MCQ Test : Check Your Understanding (5 questions)
Select an option and press Check Answer, or answer all questions and press Submit Test to see your score. Each answer comes with an explanation.
Bibliography/References
The chapter notes PDF is the course study material and is credited to its author below. The explanations, questions and MCQs on this page are original study aids written for this course from the topics covered in that PDF. The other works are cited for verification and further reading. Oracle, SQL*Plus and PL/SQL are trademarks of Oracle Corporation; other names belong to their respective owners.
- Srikanth, P. (n.d.). Oracle For Beginners (chapter notes – the PDF shown on each chapter page). Srikanth Technologies. http://www.srikanthtechnologies.com/
- Oracle Corporation. (n.d.). Oracle Database SQL Language Reference, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/
- Oracle Corporation. (n.d.). Oracle Database Concepts, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/
- Elmasri, R., & Navathe, S. B. (2016). Fundamentals of Database Systems (7th ed.). Pearson.