EXTRACT ФУНКЦИЯ
Oracle/PLSQL функция EXTRACT извлекает значение из даты или значения интервала.
Синтаксис
Синтаксис Oracle/PLSQL функции EXTRACT:
Примечание
- Функция EXTRACT возвращает numeric значение, когда предоставляются следующие параметры: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_REGION, TIMEZONE_MINUTE.
- Функция EXTRACT возвращает VARCHAR2, когда предоставляются параметры TIMEZONE_REGION или TIMEZONE_ABBR (поскольку возвращается имя часового пояса или информация об аббревиатуре).
- Вы можете извлечь только YEAR, MONTH, и DAY из даты.
- Вы можете извлечь только TIMEZONE_HOUR и TIMEZONE_MINUTE из даты/времени с типом данных часового пояса.
Применение
Функцию EXTRACT можно использовать в следующих версиях Oracle/PLSQL:
- Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i
Пример
Рассмотрим несколько примеров функции EXTRACT и изучим, как использовать функцию EXTRACT в Oracle/PLSQL.
Oracle — EXTRACT — Extract Day, Month, Year, Hours, Minutes, Seconds etc
EXTRACT function gets the specified part (day, month, year, hours, minutes etc.) from a datetime value.
Quick Example:
Get the day from January 12, 2011:
EXTRACT Function Overview
| Syntax | EXTRACT(datetime_unit FROM datetime_expression1) |
| Datetime Units | YEAR, MONTH, DAY, HOUR, MINUTE and SECOND (full. ) |
| Time Units | Although DATE contains time part, HOUR, MINUTE and SECOND can be extracted from TIMESTAMP only |
Last Update: Oracle 11g Release 2
EXTRACT Function Details
Although DATE data type contains a time part in Oracle, you cannot use EXTRACT function to get HOUR, MINUTE or SECOND from a DATE value:
So HOUR, MINUTE and SECOND units can be extracted from TIMESTAMP values only:
2 Functions to Get the Year from a Date in Oracle
Below are two functions that can be used to extract the year from a date in Oracle Database.
The EXTRACT() Function
You can use the EXTRACT(datetime) function to extract various datetime parts from a datetime value. This includes the year.
Here’s an example:
It’s the YEAR keyword that extracts the year part from the date. We can get other date parts by changing it to the relevant keyword. For example, MONTH , DAY , HOUR , MINUTE , etc.
We can also use the function with other datetime values, such as TIMESTAMP , etc.
The function can also be used to return the year from an interval. See EXTRACT(datetime) Function in Oracle for an example.
The TO_CHAR(datetime) Function
We can also use the TO_CHAR(datetime) function as an alternative method to get the year from a date.
This function accepts the datetime or interval value as its first argument, and a format model as its second argument. The function then converts the value to a data type of VARCHAR2 in the specified format.
The format model specifies the format for which to return the datetime/interval value. The format model consists of one or more format elements. This enables us to carefully craft the results to reflect our desired format.
If we only want to return the year, we can use the YYYY format element:
We can also use alternatives for the format model. For example, we can use YY to specify a two-digit year:
In fact, we can have anywhere between one and four Y characters, depending on our requirements:
Include a Comma
We can use the Y,YYY format element if we want to include a comma in the output:
Spell Out the Year
We can even use the YEAR format element to return the year spelled out:
BC Dates
We can also cater to BC dates by prefixing our year format element with an S . Doing this, prefixes the result with a minus sign, as applicable:
Extract month and year from date in oracle
what is the query for extract month and year from full date. my data is like this: 1/29/2008 I tried this query:
but It will give output: 1/1/2008
Expected output: 1-2008
6 Answers 6
If the field is already a date column, you can simply cast it to the format you want:
If it is a text column, you will need to cast to a date with format first:
A date does not have a format — it is stored internally to the database as 7-bytes (representing year, month, day, hour, minute and second) and it is not until whatever user interface you are using (i.e. SQL/Plus, SQL Developer, Java, etc) tries to display it to you, the user, and converts it into something you would find meaningful (usually a string) that the date has a format.
One thing to note is that a date always has the year, month, day, hour, minute and second components. Doing:
Is effectively the same as doing:
and will still have a day, hour, minute and second component but will have been truncated to midnight of the first day of the month. The user interface may just be have its preferences set to not display the time component (but the date will still have one).