Меню Закрыть

Oracle cast as date

Содержание

Стандартные функции SQL CAST и EXTRACT часто бывают полезными при работе с датой/временем. Функция CAST появилась в Oracle8 как механизм явного определения типов коллекций, а в Oracle8i ее возможности были расширены. Теперь функция CAST может использоваться для преобразования значений даты/времени в символьные строки, и наоборот. Функция EXTRACT , введенная в Oracle9i, позволяет выделять отдельные компоненты из значений даты/времени и интервалов.

Функция CAST

Функция CAST может использоваться для выполнения следующих операций с датой/временем:

  • преобразование символьной строки в значение даты/времени;
  • преобразование значения даты/времени в символьную строку;
  • преобразование значения одного типа даты/времени (например, DATE ) в значение другого типа (например, TIMESTAMP ).

При использовании функции CAST для преобразования значений даты/времени в символьные строки и обратно учитываются значения параметров NLS . Чтобы проверить свои настройки NLS , запросите представление V$NLS_PARAMETERS , а для их изменения используется команда ALTER SESSION . Параметры NLS для даты/времени:

  • NLS_DATE_FORMAT — используется при преобразовании в тип данных DATE и обратно;
  • NLS_TIMESTAMP_FORMAT — используется при преобразовании в типы данных TIMESTAMP и TIMESTAMP WITH LOCAL TIME ZONE .
  • NLS_TIMESTAMP_TZ_FORMAT — используется при преобразовании в тип данных TIMESTAMP WITH TIME ZONE и обратно.

В следующем примере показано, как выполняется каждый тип преобразования. Предполагается, что для NLS_DATE_FORMAT , NLS_TIMESTAMP_FORMAT и NLS_TIMESTAMP_TZ_FORMAT используются значения по умолчанию ‘DD-MON-RR’, ‘DD-MON-RR HH.MI.SSXFF AM’ и ‘DDMON- RR HH.MI.SSXFF AM TZR’ соответственно.

Здесь на основе символьной строки генерируется значение типа TIMESTAMP WITH TIME ZONE , которое преобразуется в VARCHAR2 , а затем в TIMESTAMP WITH LOCAL TIME ZONE .

Резонно спросить — зачем использовать CAST ? Действительно, эта функция частично перекрывается с функциями TO_DATE , TO_TIMESTAMP и TO_TIMESTAMP_TZ . Однако функция TO_TIMESTAMP может получать в качестве входных данных только строку, тогда как CAST может получить строку или DATE и преобразовать полученное значение в TIMESTAMP . Таким образом, CAST используется в тех случаях, когда возможностей функций TO_оказывается недостаточно. Но если задачу можно решить при помощи функции TO_ ,лучше использовать именно ее, потому что код обычно получается более понятным.

Читайте также:  Ведущие топ гир новое шоу как называется

В команде SQL при вызове CAST можно указать размер значения типа данных, например CAST (x AS VARCHAR2(40)) . Однако PL/SQL не позволяет задать размер значения целевого типа данных.

Функция EXTRACT

Функция EXTRACT используется для извлечения компонентов из значения даты/времени.

Здесь имя_компонента — имя одного из элементов даты/времени, перечисленных в табл. 1. Имена компонентов не чувствительны к регистру символов. В аргументе дата_время или интервал задается действительное значение даты/времени или интервала. Тип возвращаемого функцией значения зависит от извлекаемого компонента.

Имя компонента
Возвращаемый тип данных
YEAR NUMBER
MONTH NUMBER
DAY NUMBER
HOUR NUMBER
MINUTE NUMBER
SECOND NUMBER
TIMEZONE_HOUR NUMBER
TIMEZONE_MINUTE NUMBER
TIMEZONE_REGION VARCHAR2
TIMEZONE_ABBR VARCHAR2

В следующем примере функция EXTRACT используется для проверки того, является ли текущий месяц ноябрем:

Функцию EXTRACT удобно использовать в тех случаях, когда управление потоком выполнения программы осуществляется в зависимости от значения одного из элементов даты/времени, а также в тех случаях, когда требуется получить числовое значение одного из элементов даты/времени.

I see an inconsistency in Oracle. The inconsistency is between the way INSERT timestamp data_type value into DATE data_type column works compared to the way CAST(timestamp as DATE) works.

INSERT appears to simply cut off the milliseconds out of the timestamp value while CAST rounds them up to the closest second.

Question

Is there any easy way to avoid the rounding of milliseconds while using CAST? And I am not talking about use of TO_CHAR, TO_DATE combination with certain formatting; is there anything else? The coding with the CAST is already done, but I need a really easy fix.

2 Answers 2

To whoever is interested. I just figured it out.

Читайте также:  Sony alpha ilce 6000 объективы

There is a bug in ORACLE CAST function that makes it to behave differently when using CAST in SQL compared to using CAST in PL/SQL.

The CAST function Erroneously ROUNDS fractionals in SQL and Correctly TRUNCATES them in PL/SQL.

As we see the PL/SQL behaves the same way as the «default» conversion (insert into date select timestamp) meaning that «default» conversion is working properly as well.

The bug is fixed in 11gR2 and there is a patch available for 10g.

SQL’s CAST should (and will after the patch) TRUNCATE the fractionals instead of ROUNDING them.


Description of the illustration cast.gif

CAST converts one built-in datatype or collection-typed value into another built-in datatype or collection-typed value.

CAST lets you convert built-in datatypes or collection-typed values of one type into another built-in datatype or collection type. You can cast an unnamed operand (such as a date or the result set of a subquery) or a named collection (such as a varray or a nested table) into a type-compatible datatype or named collection. The type_name must be the name of a built-in datatype or collection type and the operand must be a built-in datatype or must evaluate to a collection value.

For the operand, expr can be either a built-in datatype, a collection type, or an instance of an ANYDATA type. If expr is an instance of an ANYDATA type, CAST will try to extract the value of the ANYDATA instance and return it if it matches the cast target type, otherwise, null will be returned. MULTISET informs Oracle Database to take the result set of the subquery and return a collection value. Table 5-1 shows which built-in datatypes can be cast into which other built-in datatypes. ( CAST does not support LONG , LONG RAW , or the Oracle-supplied types.)

Читайте также:  Лучший налобный фонарь рейтинг 2018

CAST does not directly support any of the LOB datatypes. When you use CAST to convert a CLOB value into a character datatype or a BLOB value into the RAW datatype, the database implicitly converts the LOB value to character or raw data and then explicitly casts the resulting value into the target datatype. If the resulting value is larger than the target type, then the database returns an error.

When you use CAST . MULTISET to get a collection value, each select list item in the query passed to the CAST function is converted to the corresponding attribute type of the target collection element type.

«>

Рекомендуем к прочтению

Добавить комментарий

Ваш адрес email не будет опубликован.