To char oracle формат даты

Обновлено: 07.01.2025

В предыдущей статье мы рассмотрели встроенные функции для работы со строками. В данной статье речь пойдет о функциях работы с датой/временем и функциями предобразования типов для даты. Для хранения даты и времени в Oracle предусмотрен специальный тип DATE. С физической точки зрения это дробное число, целая часть которого хранит количество дней с некоторой базовой даты, а дробная — время. Это позволяет совершать над датами арифметические операции — сложение и вычитание.

Функция SYSDATE

Это одна из самых часто употребляемых функций, она возвращает текущую дату и время по часам сервера.

SYSDATE

Функция ADD_MONTHS(d, x)

Возвращает дату, полученную в результате прибавления к дате d одного или нескольких месяцев. Количество месяцев задается параметров x, причем x может быть отрицательным — в этом случае указанное количество месяцев вычитается из заданной даты.

SELECT SYSDATE d,

ADD_MONTHS(SYSDATE, 3) d1,

ADD_MONTHS(SYSDATE, -3) d2

D

D1

D2

Функция LAST_DAY(d)

Возвращает последнее число месяца, указанного в дате d.

SELECT SYSDATE d,

D

D1

Данная функция очень удобна для определения количества дней в заданном месяце, например:

SELECT SYSDATE d,

TO_CHAR(LAST_DAY(SYSDATE), 'DD') d1

D

D1

Функция MONTHS_BETWEEN(d1, d2)

Функция MONTH_BETWEEN возвращает количество месяцев между двумя датами d1 и d2 с учетом знака как d1-d2, возвращаемое число является дробным.

SELECT MONTHS_BETWEEN('2.09.2006', '2.05.2006') d1,

MONTHS_BETWEEN('12.09.2006', '2.05.2006') d2,

MONTHS_BETWEEN('2.05.2006', '12.09.2006') d3

D1

D2

D3

Функция TRUNC(d[,mask])

Производит усечение указанной даты в соответствии с маской. Если маска не указана, то усечение производится до даты (время отбрасывается).

SELECT SYSDATE d1,

D1

D2

Рассмотрим типовые примеры — усечение даты до часов, дней, месяца и года. Форматная маска по умолчанию равна «DD»

SELECT SYSDATE d1,

TRUNC(SYSDATE, 'HH24') d2,

TRUNC(SYSDATE, 'DD') d3,

TRUNC(SYSDATE, 'MM') d4,

TRUNC(SYSDATE, 'YYYY') d5

D1

D2

D3

D4

D5

Функция ROUND(d[,mask])

Функция ROUND аналогична TRUNC, но вместо усечения она производит округление. Форматная маска по умолчанию равна «DD».

SELECT SYSDATE d1,

ROUND(SYSDATE, 'HH24') d3,

ROUND(SYSDATE, 'DD') d4,

ROUND(SYSDATE, 'MM') d5,

ROUND(SYSDATE, 'YYYY') d6

D1

D2

D3

D4

D5

D6

Форматные маски, допустимые для функций TRUNC и ROUND

Рассмотрим подробнее форматные маски и особенности их применения.

Маска

Назначение

Первый день столетия

YEAR, или YYYY, или YY, или Y

Первый день года

Первый день квартала

MONTH, или MON, или MM

Первый день месяца

Тот же день недели, что и первый день текущего года

Тот же день недели, что и первый день текущего месяца

DAY, или DY, или D

Первый день недели

HH, или HH12, или HH24

Функция TO_DATE(str[,mask [,nls_lang]])

Функция TO_DATE преобразует строку str в дату. Преобразование ведется по маске mask, если она указана. Если маска не указана, то берется маска по умолчанию. В случае указания маски можно указать еще один параметр — язык, используемый при форматировании названий месяцев и дней. В случае ошибки анализа строки str в соответствии с заданной маской возникает исключительная ситуация. Наиболее распространенная ошибка «ORA-01830: шаблон формата даты завершается перед преобразованием всей строки ввода». Кроме того, нередко встречается ошибка «ORA-01821: формат даты не распознан» — она возникает при указании недопустимой форматной маски.

SELECT TO_DATE('12.09.2006') d

D

Функция TO_CHAR(d[,mask])

Преобразует дату d в символьную строку в соответствии с заданной маской. В случае указания недопустимой маски возникает исключительная ситуация «ORA-01821: формат даты не распознан».

SELECT SYSDATE d1,

TO_CHAR(SYSDATE, 'DD.MM.YY HH24:MI') d2

D1

D2

Форматные маски, допустимые для функций TO_CHAR в случае форматирования даты

Маска

Назначение

SELECT SYSDATE d1,

TO_CHAR(SYSDATE, 'CC') d2

D1

D2

Столетие, причем перед датами до нашей эры ставится знак «минус».

SELECT SYSDATE d1,

TO_CHAR(SYSDATE-1000000, 'SCC') d2

D1

D2

SELECT SYSDATE d1,

TO_CHAR(SYSDATE, 'YYYY') d2

D1

D2

Аналогично YYYY, но перед датами до нашей эры ставится знак «минус»

Аналогичны YYYY, но возвращаются соответственно последние 3,2 или 1 цифра года.

По умолчанию Oracle выводит даты в формате DD-MON-YY, где YY - две последние цифры года:

select sysdate from dual;

При вставке в таблицу значений типа date, по умолчанию можно использовать литерал в формате

DD-MON-YYYY
(две цифры номера дня, три буквы месяца и четыре цифры года)

или использовать ключевое слово DATE для передачи в базу литерала типа data в формате ANSI

YYYY-MM-DD
(четыре цифры года, две цифры месяца, две цифры номера дня)

insert into t1 (d) values ( DATE '1971-04-28');

Конвертация даты в строку:

select to_char(sysdate) from dual;

select to_char(sysdate, 'DD') from dual; -- день

select to_char(sysdate, 'MONTH') from dual; --месяц

select to_char(sysdate, 'YYYY') from dual; -- год

select to_char(sysdate, 'HH24:MI:SS') from dual; -- часы, минуты, секунды

select to_char(sysdate, 'DD MONTH YYYY HH24:MI:SS') from dual; -- комбинация параметров формата

02 ИЮЛЬ 2014 17:00:51

select to_char(sysdate, 'CC') from dual; -- двузначное столетие (век)

select to_char(sysdate - 1000000, 'SCC') from dual; -- двузначное столетие (век), со знаком минус до нашей эры

select to_char(sysdate, 'Q') from dual; -- однозначный квартал года

Немного о стандарте ISO.

В стандарте ISO, год, относящийся к номеру недели ISO, может отличаться от календарного года.

1 января 1988 года попадает на 53-ю неделю ISO для 1987 года.
Неделя всегда начинается с понедельника и заканчивается воскресеньем.

Как связан год с номером недели по стандарту ISO:

Если 1 января падает на пятницу, субботу или воскресенье, то неделя, включающая 1 января,
считается последней неделей предыдущего года, потому что большинство дней этой недели
принадлежат предыдущему году.

Если 1 января падает на понедельник, вторник, среду или четверг, то эта неделя считается
первой неделей нового года, потому что большинство дней этой недели принадлежат новому году.

1 января 1991 падает на вторник, поэтому неделя с понедельника, 31 декабря 1990 по воскресенье, 6 января 1991 считается неделей 1.

Чтобы получить номер недели ISO, используйте маску формата 'IW' для номера недели и одну из масок вида 'IY' для года.

select to_char( DATE '1991-01-01', 'YYYY WW') from dual; -- в обычном календарном формате

select to_char( DATE '1991-01-01', 'IYYY IW') from dual; -- в формате по ISO

в данном случае результаты совпадают.

Попробуем с другой датой:

Как видим результаты разные.

При вставке в таблицу даты, рекомендуется указывать все четыре цифры года.
Если указать только две последние цифры года, то две первые цифры (столетие)
Oracle будет интерпретировать в зависимости от того, какой формат был использован при вводе.
Если использовать формат YY, то в качестве столетия будет использовано текущее столетие,
которое в настоящее время установлено на сервере.

Неважно какой год мы указали, столетие всегда будет текущее (т.е. 20)

Если использовать формат YYYY но при этом указать только две последние цифры года
то в качестве столетия Oracle подставит нули (т.е. 00)

Если использовать формат RR и указать только две последние цифры года, то две первые цифры (столетие)
Oracle будет вычислять по следующим правилам:

Если указанный год находится в интервале от 00 до 49 и текущий год тоже попадает в этот интервал,
то столетие будет текущим, но если при этом текуший год будет находится в интервале от 50 до 99,
то столетие при этом будет увеличено на 1 (текущее столетие + 1).

Если указанный год находится в интервале от 50 до 99 и текущий год тоже попадает в этот интервал,
то столетие будет текущим, но если при этом текуший год будет находится в интервале от 00 до 49,
то столетие при этом будет уменьшено на 1 (текущее столетие - 1).

Вобщем запомнить легко, если указанный год, больше текущего диапазона, значит столетие уменьшаем
и наоборот если указанный год, меньше текущего диапазона, значит столетие увеличиваем.

Интересно, а что будет если использовать формат RRRR, но при этом указать только две последние цифры года:

В качестве столетия Oracle не подставил нули, вывод аналогичен формату RR.

Для выделения первой цифры столетия в формате года можно использовать запятую:

select to_char(sysdate, 'Y,YYY') from dual; -- год с разделителем

Допустимые форматы года:

select to_char(sysdate, 'YYYY IYYY RRRR SYYYY Y,YYY YYY IYY YY IY RR Y I') from dual; -- год в различных форматах

2014 2014 2014 2014 2 014 014 014 14 14 14 4 4

А также год прописью:

select to_char(sysdate, 'YEAR') from dual; -- в верхнем регистре

select to_char(sysdate, 'Year') from dual; -- каждое слово с большой буквы

Форматы месяца:

select to_char(sysdate, 'MM') from dual; -- двузначный номер месяца

select to_char(sysdate, 'MONTH') from dual; -- полное название в верхнем регистре

select to_char(sysdate, 'Month') from dual; -- полное название с большой буквы

select to_char(sysdate, 'MON') from dual; -- три первые буквы в верхнем регистре

select to_char(sysdate, 'Mon') from dual; -- три первые буквы с большой буквы

select to_char(sysdate, 'RM') from dual; -- римскими цифрами

Форматы недели:

select to_char(sysdate, 'WW') from dual; -- двузначный номер недели года

select to_char(sysdate, 'IW') from dual; -- двузначный номер недели года по ISO

select to_char(sysdate, 'W') from dual; -- однозначный номер недели месяца

Форматы дня:

select to_char(sysdate, 'DDD') from dual; -- трехзначный номер дня года

select to_char(sysdate, 'DD') from dual; -- двузначный номер дня месяца

select to_char(sysdate, 'D') from dual; -- однозначный номер дня недели

select to_char(sysdate, 'DAY') from dual; -- полное название дня в верхнем регистре

select to_char(sysdate, 'Day') from dual; -- полное название дня с заглавной буквы

select to_char(sysdate, 'DY') from dual; -- первые две буквы названия в верхнем регистре

select to_char(sysdate, 'Dy') from dual; -- первые две буквы названия с заглавной буквы

select to_char(sysdate, 'J') from dual; -- Юлианский день - число дней, прошедшее с 1 января 4713 г. до нашей эры

Формат часов:

select to_char(sysdate, 'HH24') from dual; -- двузначный номер часа в 24 часовом формате

select to_char(sysdate, 'HH24 PM') from dual; -- с суффиксом

select to_char(sysdate, 'HH') from dual; -- двузначный номер часа в 12 часовом формате

select to_char(sysdate, 'HH PM') from dual; -- с суффиксом

select to_char(sysdate, 'HH A.M.') from dual; -- с суффиксом

Форматы минут:

select to_char(sysdate, 'MI') from dual; -- двузначное количество минут

Форматы секунд:

select to_char(sysdate, 'SS') from dual; -- двузначное количество секунд

Существует тип TIMESTAMP, который может хранить дробную часть секунд.
Необязательную точность представления секунд можно определить параметром FF[1..9]
Значение этого параметра по умолчанию равно 6 (справа от десятичной точки секунд можно поместить до 6 цифр)
При попытке поместить большее количество цифр в дробную часть секунд, значение дробной части будет округлено.

SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI.SS.FF') FROM dual; -- шесть цифр после десятичной точки (по умолчанию)

2014-10-18 08:55.42.050000

SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI.SS.FF3') FROM dual; -- три цифры после десятичной точки

2014-10-18 08:56.23.606

SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI.SS.FF9') FROM dual; -- девять цифр после десятичной точки

2014-10-18 08:56.55.526000000

select to_char(sysdate, 'SSSSS') from dual; -- число секунд отсчитываемое от полуночи

В отчетах statspack применяются следующие обозначения долей секунд:

second (s)
centisecond (cs) - 100th of a second
millisecond (ms) - 1,000th of a second
microsecond (us) - 1,000,000th of a second

Символы, позволяющие разделять аспекты дат и времени.
- / , . ; : или любой текст в кавычках "текст"

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI.SS') FROM dual;

2014-10-18 14:30.43

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD;HH24 "часов" MI "минут" SS "секунд"') FROM dual;

2014/10/18;14 часов 31 минут 18 секунд

AM или PM (A.M. или P.M.)

00:00 (полночь) 12:00 a.m.* (полночь)
12:00 (полдень) 12:00 p.m.* (полдень)

Проблемы в обозначениях полудня и полуночи:

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI.SS AM') FROM dual;

2014-10-18 14:53.58 PM

AD или BC (A.D. или B.C.)

BC - до нашей эры

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI.SS BC') FROM dual;

2014-10-18 15:00.25 Н.З.

TH - суффикс для чисел

SELECT TO_CHAR(SYSDATE, 'DDTH') FROM dual;

SELECT TO_CHAR(SYSDATE, 'ddTH') FROM dual;

SELECT TO_CHAR(SYSDATE, 'mmTH') FROM dual;

SELECT TO_CHAR(SYSDATE, 'YYYYTH') FROM dual;

SELECT TO_CHAR(SYSDATE, 'yyyyTH-MMTH-DDTH HH24TH:miTH.SSTH BC') FROM dual;

2014th-10TH-18TH 17TH:56th.52ND Н.З.

SP - числовые значения записываются словами

SELECT TO_CHAR(SYSDATE, 'DDSP') FROM dual;

SELECT TO_CHAR(SYSDATE, 'ddSP') FROM dual;

SELECT TO_CHAR(SYSDATE, 'mmTHSP') FROM dual;

SELECT TO_CHAR(SYSDATE, 'mmSP') FROM dual;

SELECT TO_CHAR(SYSDATE, 'YYYYTHSP') FROM dual;

TWO THOUSAND FOURTEENTH

SELECT TO_CHAR(SYSDATE, 'YYYYSP') FROM dual;

TWO THOUSAND FOURTEEN

EE - Полное название эпохи для японского календаря, календаря КНР и буддийского календаря.
E - Сокращенное название эпохи

select TO_DATE('H19-01-01' , 'EYY-MM-DD' , 'NLS_CALENDAR=''JAPANESE IMPERIAL''') e_date
from dual;

select TO_DATE('平成19-01-01' , 'EEYY-MM-DD' , 'NLS_CALENDAR=''JAPANESE IMPERIAL''') ee_date
from dual;

Часовые пояса:

В Oracle с версии 9i появилась возможность использовать различные часовые пояса.
Часовой пояс - это смещение от времени по Гринвичу(GMT).
Но теперь оно называется Всемирное скоординированное время(UTC).
Часовой пояс определяется либо как смещение относительно UTC, либо по имени региона (названию часового пояса).

Получить названия часовых поясов можно так:

select * from v$timezone_names;

Africa/Abidjan LMT
Africa/Abidjan GMT
Africa/Accra LMT
Africa/Accra GMT
Africa/Accra GHST
Africa/Addis_Ababa LMT
Africa/Addis_Ababa ADMT
Africa/Addis_Ababa EAT
Africa/Algiers LMT
Africa/Algiers PMT
Africa/Algiers WET
.

При определении смещения используется формат HH:MI с префиксом в виде знака + или -
+/- HH:MI

Посмотрим какое смещение относительно UTC установлено в нашей БД:

select dbtimezone from dual;

(меняется параметром time_zone в spfile.ora)

Часовой пояс сеанса можно определить так:

select sessiontimezone from dual;
Europe/Moscow

Его легко можно поменять на время сеанса:

alter session set time_zone = 'PST';
select sessiontimezone from dual;

Стандартное Тихоокеанское время PST отстает от UTC на восемь часов.
Восточное стандартное время EST отстает от UTC на пять часов.

Текущую дату для сеанса в локальном часовом поясе можно определить так:

select current_date from dual;

select to_char(current_date, 'YYYY-MM-DD HH24:MI.SS' ) from dual;

sysdate() - возвращает значение даты и времени, установленных в ОС компьютера, на котором размещена БД.
current_date() - возвращает значение даты и времени для часового пояса вашего сеанса.

Для любого часового пояса можно найти величину смещения с помощью функции tz_offset().

select tz_offset('PST') from dual;

select tz_offset('Europe/Moscow') from dual;

TZH - время в часах часового пояса
TZM - минуты часового пояса
TZR - регион часового пояса
TZD - часовой пояс с информацией о переходе на летнее время

Tип TIMESTAMP, в отличие от типа DATE, может хранить информацию о часовых поясах.

select to_char(SYSTIMESTAMP, 'TZH:TZM') from dual;

select to_char(SYSTIMESTAMP, 'TZR') from dual;

select to_char(SYSTIMESTAMP, 'TZD') from dual;

select to_char(SYSTIMESTAMP, 'HH:MI:SS.FFTZH:TZM') from dual;

select to_char(SYSTIMESTAMP, 'YYYY-MM-DD HH:MI:SS TZH:TZM') from dual;

2014-10-18 10:52:19 +04:00

select to_char(SYSTIMESTAMP, 'YYYY-MM-DD HH:MI:SS.FF AM TZH:TZM TZR TZD') from dual;

2014-10-18 10:52:31.802000 PM +04:00 +04:00

Чтобы конвертировать дату-время из одного часового пояса к другому,
можно воспользоваться функцией NEW_TIME().

Конвертация строки в тип дата-время.

Функцию TO_DATE(x [, формат])
можно использовать для конвертирования строки x в тип дата-время.

Если строка формата опущена, то дата должна быть представлена в формате по умолчанию:

DD-MON-YYYY или DD-MON-YY

(Вообще формат даты по умолчанию определяет параметр БД NLS_DATE_FORMAT)

alter session set NLS_DATE_LANGUAGE = 'AMERICAN' ;
alter session set NLS_DATE_FORMAT = 'SYYYY-MM-DD' ;
alter session set NLS_TIMESTAMP_FORMAT = 'SYYYY-MM-DD HH24:MI:SS' ;
alter session set NLS_TIMESTAMP_TZ_FORMAT = 'SYYYY-MM-DD HH24:MI:SS TZH:TZM' ;

Можно и явно задать формат

select to_date('April 28, 1971' , 'MONTH DD, YYYY') from dual;

Совместное использование to_date() и to_char()

Формат даты по умолчанию, можно использовать и при вставке строк в таблицу:

alter session set NLS_DATE_FORMAT = 'DD-MON-YYYY';

NLS - параметры:

National language_support (До Oracle9i)
Globalisation support (Начиная с Oracle9i)

Кодировка устанавливается только в переменных окружения!

Язык - RUSSIAN, AMERICAN

SELECT * FROM v$nls_valid_values
WHERE parameter = 'LANGUAGE'
ORDER BY value

CIS - СНГ
1. первый день недели
2. символ национальной валюты
(Если явно не задан параметр NLS_CURRENCY)
3. Десятичный и групповой разделители чисел

SELECT * FROM v$nls_valid_values
WHERE parameter = 'TERRITORY'
ORDER BY value

SELECT * FROM v$nls_valid_values
WHERE parameter = 'CHARACTERSET'
-- Русский язык, Кириллица
AND (value LIKE 'CL%'
OR
value LIKE 'RU%')
ORDER BY value

WE8ISO8859P1 - Западная Европа

Какие есть параметры NLS?

SELECT * FROM nls_session_parameters

PARAMETER VALUE
================ ==========
NLS_LANGUAGE=AMERICAN
NLS_TERRITORY=CIS
-- Символ нац. валюты
NLS_CURRENCY='р.'
-- Символ нац. валюты по стандарту ISO
NLS_ISO_CURRENCY='CIS'
-- Десятичный разделитель и разделитель групп
NLS_NUMERIC_CHARACTERS=', '
-- Календарь
NLS_CALENDAR=GREGORIAN
-- Формат ввода и вывода даты по-умолчанию
NLS_DATE_FORMAT='DD.MM.RR'
-- Язык для вывода названий месяцев и дней недели
NLS_DATE_LANGUAGE='AMERICAN'
-- Тип Сортировки
NLS_SORT=BINARY
-- . (нет описания)
NLS_TIME_FORMAT='HH24:MI:SSXFF'
-- Формат ввода и вывода даты типа TIMESTAMP по-умолчанию
NLS_TIMESTAMP_FORMAT='DD.MM.RR HH24:MI:SSXFF'
-- . (нет описания)
NLS_TIME_TZ_FORMAT='HH24:MI:SSXFF TZR'
-- Формат ввода и вывода даты типа TIMESTAMP с временнОй зоной по-умолчанию
NLS_TIMESTAMP_TZ_FORMAT='DD.MM.RR HH24:MI:SSXFF TZR'
-- Замещает символ нац. валюты, установленный по умолчанию параметром NLS_TERRITORY
NLS_DUAL_CURRENCY='р.'
-- Как сравнивать строки BINARY или ASCII (по правилам нац. алфавита)
NLS_COMP=BINARY
-- CHAR по умолчанию в байтах или в символах
NLS_LENGTH_SEMANTICS=BYTE
-- NLS_NCHAR_CONV_EXCP determines whether an error is reported when there is
-- data loss during an implicit OR explicit CHARACTER TYPE conversion.
-- The DEFAULT value results IN no error being reported.
NLS_NCHAR_CONV_EXCP=FALSE

Как можно устанавливать значения параметров NLS?

1. В системном реестре Windows

2. Установить переменные окружения
Для Windows (в bat-файле)

SET NLS_DATE_LANGUAGE=RUSSIAN
SET NLS_LANG=AMERICAN_CIS.CL8MSWIN1251
sqlplus .

3. ALTER SESSION SET
NLS_DATE_LANGUAGE=RUSSIAN
NLS_DATE_FORMAT='DD.MM.YYYY';

SELECT TO_CHAR(SYSDATE, 'Month day')
FROM dual

Посмотреть nls-параметры сессии, базы данных и инстанса можно так:

select * from
(select 'SESSION' SCOPE,s.* from nls_session_parameters s
union
select 'DATABASE' SCOPE,d.* from nls_database_parameters d
union
select 'INSTANCE' SCOPE,i.* from nls_instance_parameters i
) a
pivot (LISTAGG(VALUE) WITHIN GROUP (ORDER BY SCOPE)
FOR SCOPE
in ('SESSION' as "SESSION",'DATABASE' as "DATABASE",'INSTANCE' as "INSTANCE"));

Функции для работы с типом data.

ADD_MONTHS(data, n)
Позволяет добавить к дате целое количество месяцев (или отнять, если n отрицательное)

SELECT ADD_MONTHS('28.04.1971' , 13) FROM DUAL; -- Добавить 13 месяцев

SELECT ADD_MONTHS('28.04.1971' , -12) FROM DUAL; -- Отнять 12 месяцев

Помимо функций утилит SQL, встроенная библиотека функций Oracle содержит функции преобразования типов. Возможны сценарии, в которых запрос ожидает ввод данных определенного типа, но получает его в другом типе данных. В таких случаях Oracle неявно пытается преобразовать неожиданное значение в совместимый тип данных, который может быть заменен на месте, и непрерывность приложения не нарушается. Преобразование типов может быть неявно выполнено Oracle или явно программистом.

Неявное преобразование типов данных работает на основе матрицы, которая демонстрирует поддержку Oracle для внутреннего приведения типов. Помимо этих правил, Oracle предлагает функции преобразования типов, которые можно использовать в запросах для явного преобразования и форматирования. На самом деле, рекомендуется выполнять явное преобразование, а не полагаться на программный интеллект. Несмотря на то, что неявное преобразование работает хорошо, но для устранения перекосных шансов, где плохие входные данные может быть сложно ввести внутренне.

Неявное преобразование типов данных

Значение VARCHAR2 или CHAR может быть неявно преобразовано в значение типа NUMBER или DATE с помощью Oracle. Точно так же значение типа NUMBER или DATA может быть автоматически преобразовано в символьные данные сервером Oracle. Обратите внимание, что недопустимое взаимопревращение происходит только тогда, когда символ представляет действительное число или значение типа даты соответственно.

Например, рассмотрите следующие запросы SELECT. Оба запроса дадут один и тот же результат, потому что Oracle внутренне обрабатывает 15000 и «15000» как одинаковые.

Query-1

Запрос-2

Явное преобразование типов данных

Функция TO_CHAR

Функция TO_CHAR используется для ввода числового значения или даты в тип символа с помощью модели формата (необязательно).

Синтаксис

Даты могут быть отформатированы в нескольких форматах после преобразования в типы символов с помощью функции TO_CHAR. Функция TO_CHAR используется для отображения дат Oracle 11g в определенном формате. Форматные модели чувствительны к регистру и должны быть заключены в одинарные кавычки.

Рассмотрим приведенный ниже запрос SELECT. Формат запроса в столбцах HIRE_DATE и SALARY таблицы EMPLOYEES с использованием функции TO_CHAR.

Первый TO_CHAR используется для преобразования даты приема в формат даты МЕСЯЦ ДД, ГГГГ, т. Е. Месяц, прописанный с пробелами, за которым следует двузначный день месяца, а затем четырехзначный год. Если вы предпочитаете отображать название месяца в смешанном регистре (то есть «декабрь»), просто используйте этот регистр в аргументе формата: («Месяц ДД, ГГГГ»).

Вторая функция TO_CHAR на рисунке 10-39 используется для форматирования SALARY для отображения знака валюты и двух десятичных позиций.

Oracle предлагает полный набор форматных моделей. В таблице ниже приведен список моделей формата, которые можно использовать для ввода даты и числовых значений в виде символов с использованием TO_CHAR.

Функция TO_NUMBER

Функция TO_NUMBER преобразует символьное значение в числовой тип данных. Если преобразуемая строка содержит нечисловые символы, функция возвращает ошибку.

Синтаксис

В приведенной ниже таблице показан список моделей форматов, которые можно использовать для ввода значений символов в виде числа с использованием TO_NUMBER.

Приведенные ниже запросы SELECT принимают числа в качестве ввода символов и распечатывают их в соответствии со спецификатором формата.

Функция TO_DATE

Функция принимает символьные значения в качестве входных данных и возвращает форматированную дату, эквивалентную той же самой. Функция TO_DATE позволяет пользователям вводить дату в любом формате, а затем преобразует запись в формат по умолчанию, используемый Oracle 11g.

Синтаксис:

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

Строчные функции конвертации типа данных разработаны для изменения типа данных столбца, выражения или литерала. Наиболее часто используемые функции конвертации это TO_CHAR, TO_NUMBER и TO_DATE. TO_CHAR преобразует числа и даты в символьные данные, когда TO_NUMBER и TO_DATE преобразует символьные данные соответственно в число и дату.

Функции конвертации

Oracle позволяет определять столбцы с типами данных ANSI, DB2 и SQL/DS. Эти типы преобразуется к типам данных Oracle. У каждого столбца определяется тип данных который ограничивает природу данных которые могут храниться в этом столбце. Столбец NUMBER не может хранить символьную информацию. Столбец DATE не может хранить случайные символы или числа. VARCHAR2 может хранить символьные эквиваленты чисел и дат.

Если функция у которой входной параметр является строкой получает на вход число, Oracle автоматически конвертирует его в символьный эквивалент. Если функция с входными параметрами типа данных число или дата получает на вход строку, то должны соблюдаться определённые условия для автоматической конвертации. Несмотря на то что автоматическая неявная конвертация доступна, гораздо более понятно и надёжно явно сконвертировать один тип данных в другой перед вызовом строчной функции.

Неявная конвертация типов

Значения, которые не соответствуют типам данных параметров функции неявно конвертируется перед выполнением если это возможно. Оба типа данных VARCHAR2 и CHAR используются как символьные типы данных. Символьные типы данных достаточно гибкие для хранения практически любой информации. Таким образом, ДАТА и ЧИСЛО можно легко преобразовать в их символьный эквивалент. Такая конвертация известна как преобразования число в строку и дата в строку. Рассмотрим следующие запросы

Query 1: select length(1234567890) from dual

Query 2: select length(SYSDATE) from dual

Оба запроса используют функцию LENGTH у которой входной параметр определён как строка. Число 1234567890 в запросе один неявно конвертируется в строку ‘1234567890’ перед вычисление функции LENGTH и результат функции будет 10. Запрос номер два вычисляет функцию SYSDATE предположим 7 апреля 2008 года. Результат преобразуется в строку ’07-APR-08’ и результат выполнения функции LENGTH будет число 9.

Обычно не принято допускать неявную конвертацию строк в числа, так как единственная ситуация, когда это возможно, это если строка представляет собой валидное число. Строка ‘11’ будет неявно преобразована в число 11, но строка ’11.123.345’ не будет, как показано в следующих примерах

Запросы 3 и 4 неявно преобразовали строки ‘11’ и ’11.123’ в числа 11 и 11.123 соответственно, перед вызовом функции MOD которая в свою очередь вернула результат 1 и 1.123. Запрос 5 вернул ошибку ‘ORA-1722: invalid number’, когда Oracle попытался неявно преобразовать строку в число, так как ‘11.123.456’ не является корректным числом. Запрос 6 также вернул ошибку так как символ доллара не может бять неявно преобразован в число.

Неявная конвертация строки в дату возможна, когда строка удовлетворяет следующим шаблонам: [D|DD] separator1 [MON|MONTH] separator2][R|RR|YY|YYYY], где D и DD это день MON первые три буквы месяца, MONTH – полное название месяца. R и RR YY и YYYY отображают одну, две и четыре цифры года соответственно. Параметром separator1 и separator2 может быть практически любой спец символ, включая сюда пробел, табуляцию, знаки пунктуации и т.д. Таблица 10-2 показывает неявную конвертацию строки в дату, включая вызов функций работы с датами и результаты. Эти результаты предполагают, что система использует американскую локаль.

TIP Несмотря на то что неявная конвертация типов доступна, лучше использовать явный вызов функции конвертации перед использование данным в других функциях. Конвертация строки в число и дату использует маску форматирования.

Явная конвертация типов данных

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

Число и дату можно явно конвертировать в строку используя функцию TO_CHAR. Строку можно явно конвертировать в число используя функцию TO_NUMBER. Функция TO_DATE используется для конвертации строку в DATE. Маски форматирования Oracle позволяют гибко контролировать процесс конвертации строки в число или дату.

Использование функций TO_CHAR, TO_NUMBER и TO_DATE

Использование функций конвертации

Во многих ситуациях возникает потребность в использовании функций конвертации, начиная от форматирования даты в отчётах и до проверки численных значений в символьных столбцах перед стартом арифметических вычислений.

Таблица 10-3 показывает синтаксис строчных функций конвертации данных

Необязательный параметр поддержки национального формата (nls_parameters) полезен для указания языка и форматирования, в котором названия дней, месяцев и разделители разрядов, целой и дробной части заранее предопределены. На рисунке 10-2 отображено представление NLS_SESSION_PARAMETERS которое содержит значения параментов NLS для текущей сессии. По умолчанию значение NLS_CURRENCY – знак доллара, но это можно изменить на уровне сессии. Например, для изменения символа валюты на строку ‘GBP’ можно выполнить запрос

ALTER SESSION SET NLS_CURRENCY=’GBP’

Рисунок 10-2 – Представление NLS_SESSION_PARAMETERS

Конвертация числа в строку используя функцию TO_CHAR

Функция TO_CHAR возвращает значение типа VARCHAR2. Когда входных параметром является число то доступны некоторые параметры форматирования. Синтаксис команды TO_CHAR(num, [format], [nls_parameter]). Параметр num обязательный и должен быть числом. Необязательный параметр format можно использовать для указания информации о форматировании, такой как длина, символ валюты, позиция разделителя дробной и целой части и разделитель разрядов (три разряда) и должен быть заключен в одинарные кавычки. Доступны различные опции форматирования и часть из них представлена в таблице 10-4. Рассмотрим два запроса

Tip Конвертация чисел в строки надёжный способ убедиться что функция и SQL запрос в целом, который ожидает символьного значения, не вернёт ошибку когда встретится число. Конвертация чисел в строки часто используется для форматирования значений для отчетов. Маска форматирования поддерживает символ валюты, разделитель порядков и разделитель целой и дробной части, что часто используется при отображении финансовой информации.

Конвертация даты в строку используя функцию TO_CHAR

Вы можете использовать преимущества модели масок форматирования при конвертации ДАТЫ в практически любой вариант отображения даты как символьного значения используя функцию TO_CHAR. Синтаксис функции TO_CHAR(date1, [format], [nls_parameter]).

Только параметр date1 обязательный; тогда он должен быть значением, которое может неявно преобразоваться в строку. Необязательный параметр format регистрозависимый и должен быть обрамлён одинарными кавычками. Маска форматирования указывает какие лементы даты должы быть выбраны и как отображать названия элементов даты: полные названия или аббревиатуры. Названия дней и месяцев автоматически разделяются пробелом. Такое поведение можно изменить, используя параметр маски fill mode (fm). Указав в начале маски параметр fm, вы укажете Oracle о необходимости убрать все пробелы. Доступно много опций для маски форматирования, часть из которых отображена в таблице 10-5. Рассмотрим три запроса

Если текущая системная дата 3 января 2009 года и по умолчанию формат отображения DD/MON/RR тогда запрос один вернёт строку ‘03/JAN/09 is todays date’. Во втором запросе обратите внимание на две детали: во-первых, только месяц выбирается из даты, и во-вторых так как маска форматирования регистрозавсимая и в запросе используется ‘Month’, то запрос вернёт ‘January is a special time’. Нет нужды добавлять пробел в начале литерала, так как функция TO_CHAR автоматически добавит пробел к названию месяца. Если бы маска во втором запросе была ‘MONTH’ то запрос вернул бы ‘JANUARY is a special time’. Параметр fm в третьем запросе препятствует добавлению пробелов и результатом будет ‘Januaryis a special time’. В таблице 10-5 предполагается что обрабатывается дата 2 июня 1975 года и текущий год 2009.

Параметры форматирования, связанные с неделей, кварталом, веком и другими более редко использующимися элементами даты показаны на рисунке 10-7. Столбец результата предполагает, что функция использовалась для работы с датой 24 сентября 1000 года, с маской форматирования указанной в столбец format element.

Компонент время в типе данных дата выбирается, используя модели форматирования в таблице 10-7. Результат рассчитывается функцией TO_CHAR используя дату 27 июня 2010 года время 21:35:13 с маской форматирования указанной в столбце format element.

Некоторые различные элементы, которые можно использовать в форматировании даты и времени перечислены в таблице 10-8. Знаки пунктуации используются для разделения элементов форматирования. Три типа суффиксов существуют для форматирования элементов. Более того, символьные литералы могут быть включены в модель форматирования если они заключены в двойные кавычки. Результаты в таблице 10-8 получены используя функцию TO_CHAR для даты 12 сентября 2008 года 14:31 с маской форматирования указанной в соответствующем столбце.

Рисунок 10-3 – Запрос в таблицу JOB_HISTORY

ORDER BY END_DATE;

Несмотря на то что компонент «век» не отображается по умолчанию, он хранится в базе данных и доступен для запроса. Маска форматирования DD-MON-RR используется по умолчанию для ввода значений и отображения. Когда значение добавляется или изменяется если явно неуказан век, то используется век из функции SYSDATE. Формат RR отличается от формата YY и так как RR также использует значение столетия. Влияние значения столетия на формат RR легче понять если рассмотреть следующие принципы

• Если две последние цифры текущего года между 0 и 49, а в указанном значении даты две последние цифры года между 50 и 99 то используется предыдущий век. Предположим, что текущая дата 2 июня 2007 года. Значение века для даты 24-JUNE-94 года будет 20
• Если две цифры текущего года между 50-99 и указанной даты также между 55 и 99, то возвращается текущий век. Преположим что текущая дата 2 июня 1975 года. Тогда значение века для 24-JUL-94 будет 20.
• Если две цифры текущей даты между 50 и 99, а в укащанной дате год между 0 и 49 – то считается следующий век. Предположим, что текущая дата 2 июня 1975 года, тогда для значения 24-JUL-07 значение века будет 21.

Конвертация строки в дату используя функцию TO_DATE

Функция TO_DATE возвращает значение типа данных DATE. Строка, конвертируемая в дату может содержать все или часть компонентов, составляющих тип DATE. Когда строка содержащая только часть компонентов даты преобразуется в дату, Oracle использует значение по умолчанию для составления валидного значения типа DATE. Части строки сопоставляются с элементами даты используя маску (или модель) форматирования. Синтаксис функции TO_DATE(string1, [format], [nls_parameter]).

Только параметра string1 обязателен, и eсли маска форматирования не указана, string1 должна быть в формат неявно конвертируемом в дату. Необязательный параметр format используется практически всегда и должен быть заключён в одинарные кавычки. Маска форматирования идентича перечисленным в таблицах 10-5, 10-6, и 10-7. У функции TO_DATE есть модификатор fx, которые используется подобно параметру fm функции TO_CHAR. Параметр fx требует обязательного совпадения строки и маски форматирования. Если строка не совпадает с маской – возвращается ошибка. Рассмотрим несколько примеров

Конвертация строки в число используя функцию TO_NUMBER

Функция TO_NUMBER возвращает значение типа данных NUMBER. Исходная строка должна быть составлена таким образом, чтобы все несовместимые символы отсутствовали или были указаны в соответствующей маске форматирования. Синтаксис функции TO_NUMBER(string1, [format], [nls_parameter]). Только string1 является обязательным параметром, и если не указан параметр format то значение должно быть таким, чтобы была возможность неявно сконвертировать его в число. Маски форматирования идентичны перечисленным в таблице 10-4. Рассмотрим запросы

Запрос один не может неявно преобразовать строку, так как она содержит знак валюты и разделители, которые явно неуказаны в маске, поэтому возвращается ошибка ORA-1722: invalid number. Запрос два находит символ валюты, запятой и точки в маске форматирования и несмотря на то что длина маски больше чем чем исходное значение в строке, возвращается число 1000.55

Функция TO_NUMBER конвертирует значение строки в число. Если вы используете число длиннее чем маска, возвращается ошибка. Если вы конвертируете число используя более длинную маску – возвращается значение исходной длины. Не путайте TO_NUMBER с TO_CHAR. Например TO_NUMBER(‘123.45’,’999.9’) вернёт ошибку, когда TO_CHAR(123.45,’999.9’) вернёт 123.6

Читайте также:

  
• Robotdemo exe вирус как удалить с компьютера
  
• Поиск инн по названию организации excel
  
• Acer 8930g при обновлении биос слетел серийный номер
  
• Как посмотреть код программы на windows
  
• Redmi 8 не работают динамик и микрофон в программе телефон