To get the "month" from a DATE datatype you simply:
We must remember that these "built in functions" will cause the SQL optimizer to perform a full-table scan unless a function-based index is created to match the BIF.
The most common example of an Oracle BIF is the use of the to_char
function to translate a column with a DATE datatype. For example, the following query will display the number of STATSPACK snapshots that occurred in the month of March..
to_char(snap_time,'MON') = 'Mar';
If we were using a generic index on the snap_time column, the to_char function would not be able to utilize the DATE index. However, with the use of built-in functions, an index can be built on
and the SQL query could avoid a full-table scan