Note: Throughout this course, the words keyword, clause, and statement are used as follows:
- A keyword refers to an individual SQL element. For example, SELECT and FROM are keywords.
- A clause is a part of a SQL statement. For example, SELECT employee_id, last_name, … is a clause.
- A statement is a combination of two or more clauses. For example, SELECT * FROM employees is a SQL statement.
Arithmetic with Dates
| Operation |
Result |
Description |
| date + number |
Date |
Adds a number of days to a date |
| date – number |
Date |
Subtracts a number of days from a date |
| date – date |
Number of days |
Subtracts one date from another |
| date + number/24 |
Date |
Adds a number of hours to a date |
Using Date Functions
Assume SYSDATE = ’25-JUL-03′
| Function |
Result |
| ROUND(SYSDATE,’MONTH’) |
01-AUG-03 |
| ROUND(SYSDATE ,’YEAR’) |
01-JAN-04 |
| TRUNC(SYSDATE ,’MONTH’) |
01-JUL-03 |
| TRUNC(SYSDATE ,’YEAR’) |
01-JAN-03 |
| MONTHS_BETWEEN (’01-SEP-95′,’11-JAN-94′) |
19.6774194 |
| ADD_MONTHS (’11-JAN-94′,6) |
’11-JUL-94′ |
| NEXT_DAY (’01-SEP-95′,’FRIDAY’) |
’08-SEP-95′ |
| LAST_DAY (’01-FEB-95′) |
’28-FEB-95′ |
General Function
- NVL Converts a null value to an actual value. Example: NVL(number_column,9), NVL(date_column, ’01-JAN-95′), NVL(character_column, ‘Unavailable’)
- NVL2 If expr1 is not null, NVL2 returns expr2. If expr1 is null, NVL2 returns expr3. The argument expr1 can have any data type. Example: NVL2(commission_pct, ‘SAL+COMM’, ‘SAL’)
continue…
