FUNCTIONS

ADD_MONTHS

Add a number of months (n) to a date and return the same day which is n of months away.

WHAT AGGREGATE FUNCTIONS IGNORE NULL VALUES?

All aggregate functions ignore null values except COUNT(*), GROUPING(), and GROUPING_ID().

TO_CHAR

Convert a DATE or an INTERVAL value to a character string in a specified format.

NEW_TIME

Convert a date in one time zone to another

TO_DATE

Convert a date which is in the character string to a DATE value.

EXTRACT (IS A DATE FUNCTIONS)

Extract a value of a date time field e.g., YEAR, MONTH, DAY, ... from a date time value.

DBTIMEZONE

Get the current database time zone

NEXT_DAY

Get the first weekday that is later than a specified date.

LAST_DAY

Gets the last day of the month of a specified date.

To effectively use the ROW_NUMBER() function, you should use a subquery or a common table expression to retrieve row numbers for a specified range to get the top-N, bottom-N, and inner-N results.

NOTE

WHERE CAN AGGREGATE FUNCTIONS APPEAR?

Oracle aggregate functions can appear in SELECT lists and ORDER BY, GROUP BY, and HAVING clauses.

LOCALTIMESTAMP

Return a TIMESTAMP value that represents the current date and time in the session time zone.

ROUND

Return a date rounded to a specific unit of measure

CURRENT_DATE

Return the current date and time in the session time zone

CURRENT_TIMESTAMP

Return the current date and time with time zone in the session time zone

SYSDATE

Return the current system date and time of the operating system where the Oracle Database resides.

MONTHS_BETWEEN

Return the number of months between two dates.

SYSTIMESTAMP

Return the system date and time that includes fractional seconds and time zone.

WHAT AGGREGATE FUNCTIONS CAN RETURN NULLS?

The COUNT() and REGR_COUNT() functions never return null, but either a number or zero (0). Other aggregate functions return NULL if the input data set contains NULL or has no rows.

ROW_NUMBER

The ROW_NUMBER() is an analytic function that assigns a sequential unique integer to each row to which it is applied, either each row in the partition or each row in the result set.

FIRST_VALUE

The expression must return a single value and must not contain subqueries or other analytic functions. The FIRST_VALUE() function returns NULL if the value of the first row in the window frame evaluates to NULL unless you specify the IGNORE NULLS option. By default, the function uses RESPECT NULLS.