oracle количество дней в месяце

Oracle количество дней в месяце

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

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

SELECT SYSDATE
FROM dual

SYSDATE
26.12.2007 16:24:43

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

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

Функция LAST _DAY(d)

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

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

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

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

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

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

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

26.09.2006 01.09.2006 01.01.2006

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

27.09.2006 26.09.2006 17:00:00

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

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

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

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

Тот же день недели, что день текущего года

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

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

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

Функция ТО_DATE преобразует строку str в дату. Преобразование ве­дется по маске mask, если она указана. Если маска не указана, то берется маска по умолчанию. В случае указания маски можно указать еще один параметр — язык, используемый при форматировании названий месяцев и дней. В случае ошибки анализа строки str в соответствии с заданной

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

SELECT T0_DATE(‘12.09.2006’) d
FROM dual

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

SELECT SYSDATE d1,
TOLCHAR(SYSDATE, ‘DD.MM.YY HH24:MI’) d2
FROM dual

© 2021 Научная библиотека

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

Источник

Введение в Oracle 10g

Введение

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

Функция SYSDATE

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

SYSDATE

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

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

ADD_MONTHS(SYSDATE, 3) d1,

Функция LAST_DAY(d)

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

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

TO_CHAR(LAST_DAY(SYSDATE), ‘DD’) 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

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

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

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

TRUNC(SYSDATE, ‘HH24’) d2,

TRUNC(SYSDATE, ‘DD’) d3,

TRUNC(SYSDATE, ‘MM’) d4,

TRUNC(SYSDATE, ‘YYYY’) d5

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

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

ROUND(SYSDATE, ‘HH24’) d3,

ROUND(SYSDATE, ‘DD’) d4,

ROUND(SYSDATE, ‘MM’) d5,

ROUND(SYSDATE, ‘YYYY’) d6

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

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

Маска

Назначение

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

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

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

MONTH, или MON, или MM

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

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

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

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

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

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

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

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

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

Маска

Назначение

TO_CHAR(SYSDATE, ‘CC’) d2

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

TO_CHAR(SYSDATE-1000000, ‘SCC’) d2

TO_CHAR(SYSDATE, ‘YYYY’) d2

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

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

Источник

Мой блог

среда, 2 июля 2014 г.

Дата и время в Oracle

Форматы даты по умолчанию:

select sysdate from dual;

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

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

insert into t1 (d) values (’28-APR-1971′);

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

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

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

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

select to_char(sysdate) from dual;

02 ИЮЛЬ 2014 17:00:51

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

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

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

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

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

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

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

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

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

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

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

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

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

select
to_char(to_date(’28-04-14′, ‘DD-MM-YY‘), ‘DD-MM-YYYY’),
to_char(to_date(’28-04-77′, ‘DD-MM-YY‘), ‘DD-MM-YYYY’)
from dual;

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

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

select
to_char(to_date(’28-04-14′, ‘DD-MM-YYYY‘), ‘DD-MM-YYYY’),
to_char(to_date(’28-04-77′, ‘DD-MM-YYYY‘), ‘DD-MM-YYYY’)
from dual;

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

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

select
to_char(to_date(’28-04-14′, ‘DD-MM-RR’), ‘DD-MM-YYYY’),
to_char(to_date(’28-04-77′, ‘DD-MM-RR‘), ‘DD-MM-YYYY’)
from dual;

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

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

select
to_char(to_date(’28-04-14′, ‘DD-MM-RRRR‘), ‘DD-MM-YYYY’),
to_char(to_date(’28-04-77′, ‘DD-MM-RRRR‘), ‘DD-MM-YYYY’)
from dual;

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

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

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

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

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

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

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

SELECT TO_CHAR(SYSDATE, ‘YYYY—MM—DD HH24:MI.SS’) FROM dual;

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

12-часовой формат исчисления времени предполагает разбиение 24 часов, составляющих сутки,
на два 12-часовых интервала, обозначаемых a.m. (лат. ante meridiem дословно — «до полудня»)
и p.m. (лат. post meridiem дословно — «после полудня»).

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

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

Несмотря на наличие международного стандарта ISO 8601, 12 часов ночи и 12 часов дня обозначается в разных
странах по-разному. Это связано с тем, что в латинских словосочетаниях лат. ante meridiem и
лат. post meridiem слово meridiem означает буквально «середина дня» или «полдень»,
и нет однозначности между обозначением полудня как «12 a.m.» («12 ante meridiem»,
или «12 часов до середины дня») или как «12 p.m.» («12 post meridiem», или «12 часов после середины дня»).

С другой стороны, полночь также можно логично назвать «12 p.m.» (12 post meridiem,
12 часов после предыдущей середины дня) или «12 a.m.» (12 ante meridiem, 12 часов до следующей середины дня).

National Maritime Museum в Гринвиче рекомендует обозначать эти временные моменты как «12 дня» и «12 ночи».
То же советует и The American Heritage Dictionary of the English Language. Многие руководства по стилю,
принятые в США, предлагают «полночь» заменять на «11:59 p.m.», если мы хотим обозначить конец дня,
и «12:01 a.m.», если мы хотим обозначить начало следующего дня.

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

2014-10-18 14:53.58 PM

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

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

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;

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

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

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
.

Посмотрим какое смещение относительно 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;

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

select tz_offset(‘PST’) from dual;

select tz_offset(‘Europe/Moscow’) from dual;

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’ ;

alter session set NLS_DATE_LANGUAGE = ‘AMERICAN’;
alter session set NLS_DATE_FORMAT = ‘DD-MON-RRRR’;
select to_date(’28-APR-1971′), to_date(’28-APR-71′) from dual;

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

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

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

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

insert into t1 ( id, bday ) values (1, ‘28-APR-1971‘ );

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

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

1) Язык вывода сообщений об ошибках
2) на каком языке выводить названия месяцев и дней недели
(Если явно не задан параметр NLS_DATE_LANGUAGE)

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

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

NLS_LANG = AMERICAN_CIS.CL8MSWIN1251
NLS_LANG = AMERICAN_AMERICA.RU8PC866
NLS_LANG = RUSSIAN_CIS.CL8ISO8859P1

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

SELECT * FROM nls_session_parameters

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

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

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

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 отрицательное)

В функцию ADD_MONTHS() можно передать и дату и время:

SELECT
TO_CHAR(ADD_MONTHS(TO_DATE(‘31.10.2001 21:08:50‘, ‘fxDD.MM.YYYY HH24:MI:SS’)
, 4) — Добавить 4 месяца
, ‘DD.MM.YYYY HH24:MI:SS’)
FROM DUAL;

SELECT
TO_CHAR(ADD_MONTHS(TO_DATE(‘28.02.2001 21:08:50‘, ‘fxDD.MM.YYYY HH24:MI:SS’)
, -4) — Отнять 4 месяца
, ‘DD.MM.YYYY HH24:MI:SS’)
FROM DUAL;

Источник

Некоторые примеры нестандартных возможностей синтаксиса sql. Часть вторая: форматы дат

Введение

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

Изначально я не планировал написание статьи о датах, а собирался остановиться на рассмотрении всего одного вопроса данной тематики. Однако в ходе работы возникла необходимость в освещении различных дополнительных возможностей Oracle, стали появляться новые примеры. Так рассмотрение одного вопроса разрослось в небольшую статью. Надеюсь, получилось нескучно, несмотря на не самую интересную тематику.

Первая часть статьи, посвященная особенностям оператора order by, функционированию not in и примеру неявного преобразования типов, находится здесь.

Функция to_date и форматы даты

Мало кто из программистов любит тематику форматирования. Например, на некоторых курсах темы форматирования дат и региональных стандартов специально ставят на последние часы последнего дня тренинга, т.к. слушателям нудно. Причина в большом количестве существующих форматов при относительно редком их использовании в стандартных задачах. Чаще всего маски используются в трех функциях: to_number, to_char и to_date. Во всех трех случаях маска идет вторым необязательным параметром. И если масок для форматирования чисел еще более-менее вменяемое количество, то масок для форматирования дат до неприличия много, плюс еще суффиксы и модификаторы.

Безусловно, доступность большого количества масок является позитивным моментом, поскольку расширяет возможности, например, проверить является ли 13 сентября 2011 днем программиста, можно с помощью маски ‘DDD’, которая возвращает номер дня в году:

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

Перед тем как перейти непосредственно к основной теме раздела, давайте рассмотрим несколько нестандартных примеров работы с датами.

Пример №1. Использование урезанных шаблонов
Начнем со стандартного форматирования. Пускай сегодняшняя дата 16.09.2011, выполнятся ли следующие запросы, и что они вернут?

Запрос №2 является типичным примером конвертации даты в строку с приведением ее к нужному формату. Единственное отличие – вместо более привычных масок вида ‘DD.MM.YY’ или ‘DD-MON-YYYY’ мы использовали маску, задающую только год. Запрос №2 выполнится успешно и вернет текущий год в четырехзначном формате, т.е. ‘2011’.

Запрос №3 немного интереснее, он представляет собой типичный пример явного преобразования строки в дату с урезанной маской формата, поэтому, с точки зрения синтаксиса, запрос верный и выполнится успешно. Более важным вопросом является результат его выполнения, т.е. какую дату он вернет, если задан только день? Перед ответом на данный вопрос давайте вспомним, как Oracle устанавливает время, если оно явно не задано:

В запросе №4 время не указано, в запросе №5 указано только количество минут, часы и секунды опущены. В Oracle существует правило, согласно которому, если в дате отсутствует временной компонент, то время автоматически устанавливается в значение 00:00:00 (т.е. полночь), если задана только часть элементов времени (как в запросе №5), то пропущенные элементы устанавливаются в 00. Поэтому, запрос №4 вернет строку ‘03.02.2011 00:00:00’, а запрос №5 — ‘03.02.2011 00:30:00’.

Вернемся к запросу №3, верно ли данное правило для дат, т.е. заменяются ли пропущенные при конвертации элементы даты на 00 или 01? Да заменяются, но не все, точнее, для пропущенных элементов даты используются значения из sysdate (первый день текущего месяца текущего года). Поэтому запрос №3 будет использовать 09 в качестве месяца и 2011 в качестве года, таким образом, результатом выполнения запроса будет дата 03.09.2011.

Пример №2. Порядок параметров форматирования
Выполнится ли следующий запрос, и если да, то какую дату он вернет?

На первый взгляд, отсутствие разделителей в строке с датой может показаться критическим фактором несовместимым с выполнением запроса, однако маска даты также задана без разделителей и строка для преобразования соответствует указанному шаблону. Поэтому запрос №6 выполнится успешно и вернет 20.11.2009 (формат результата может несколько отличаться в зависимости от настроек сессии). Детальнее вопросы, связанные с разделителями, мы рассмотрим в следующем примере.

Пример №3. Неявная конвертация
Пусть формат даты по умолчанию DD.MON.RR, а язык даты – русский, отработает ли следующий запрос:

В данном запросе указано два строковых параметра, которые должны быть преобразованы в даты с помощью неявной конвертации. Согласно документации, при использовании форматов по умолчанию, строка для неявного преобразования в дату должна удовлетворять шаблону: [D|DD] separator1 [MM|MON|MONTH] separator2 [R|RR|YY|YYYY]. В качестве separator1 и separator2 можно использовать большинство разделительных знаков и специальных символов, в том числе пробелы, табуляцию, а также » и удвоенную одинарную кавычку ». Более того, если в строке указано не менее двух цифр для задания дней, месяцев и лет, то separator вообще может быть опущен. Например:

Поскольку обе строки указанные в запросе №7 соответствуют приведенному шаблону, то запрос выполнится успешно и вернет число 11.

Пример №4. Параметры функции to_date
Пусть формат даты по умолчанию DD.MON.RR, а язык даты – русский, отработает ли следующий запрос:

Схожий запрос фигурировал в одном из обсуждений на странице ask Tom. Ловушка запроса в том, что мы пытаемся преобразовать дату (sysdate) в дату. Если бы запрос выглядел так:

То выполнение прошло бы успешно, и он вернул строку ’09/15/2011 23:00:11′. Однако функция to_date в качестве первого параметра ожидает строку поэтому, вначале происходит неявная конвертация даты в строку (что эквивалентно вызову to_char(sysdate) с маской по умолчанию). Результатом данной конвертации является строка ‘15.09.11’, далее происходит вызов to_date. Таким образом, запрос №11 эквивалентен следующему запросу:

Как не сложно убедиться, запрос №13 не может быть выполнен, поскольку строка ‘15.09.11’ не соответствует установленной маске, соответственно, запрос №11 так же не может быть выполнен.

Установка формата даты по умолчанию
Формат дат по умолчанию задается двумя параметрами: NLS_DATE_FORMAT (отвечает за сам формат как таковой) и NLS_DATE_LANGUAGE (отвечает за язык, который будет использован при написании названий дней, месяцев и т.д.). Если эти параметры не заданы явно, то их значения устанавливаются на основе параметра NLS_LANG.

Логично предположить, что преобразование строки ‘11.09.11’ в дату пройдет успешно, а строки ’11.SEP.11′ – нет. Однако это не так, успешно выполнятся оба преобразования. Вначале я предполагал, что в случае невозможности преобразовать строку по маске сессии Oracle пытается задействовать маски других уровней (маска уровня БД у меня установлена в ‘DD-MON-RR’). Чтение документации показало, что это не так, и Oracle руководствуется принципами, описанными в предыдущем пункте.

Попробуем другой пример:

Если вы думаете, что результат будет идентичен предыдущему запросу, то вы ошибаетесь. Одно из преобразований не выполнится. В данном случае строка ‘11.09.11’ не удовлетворяет шаблону. Возможно, это мистика?

Увы, нет. Чтение документации показало, что существуют правила автозамены элементов форматирования даты. Ниже привожу таблицу замен.

Глядя на содержимое этой таблицы, становится понятно, что в формате ‘DD.MM.RR’ неявным образом присутствует формат ‘DD.MON.RR’ (а также ‘DD.MONTH.RR’ и другие), а вот в формате ‘DD.MON.RR’ формат ‘DD.MM.RR’ не присутствует, что и объясняет поведение запросов №14 и №15.

Пример №6. Формат RR vs YY
Большинству пользователей отличия масок RR и YY хорошо известны, однако есть и те, кому данная информация окажется полезной. Перейдем сразу к рассмотрению примера. Какие данные вернут следующие запросы:

Оба приведенных выше запроса выполнятся успешно и вернут даты в соответствии с правилами, описанными в примере №1 для запроса №3. Таким образом, значение дня во всех полученных датах будет равно 01, а значение месяца 09 (если вы выполняете запрос в сентябре). Главный вопрос, каким будет значение года?

Как несложно предположить, в запросе №16 под ’11’ я подразумевал 2011 год и обе маски мне его вернули, т.е. результат выполнения запроса №16 это 01.09.2011 и 01.09.2011.

В запросе №17 под ’99’ я подразумевал 1999 год и тут мнения масок разделились: маска RR вернула ожидаемый 1999 год, а маска YY – 2099, т.е. результат выполнения запроса №17 это 01.09.1999 и 01.09.2099.

Рассмотрим, как работают эти элементы форматирования более детально:

Поэтому запрос №19 вернет 1950 год в обоих случаях.

Пример №7. Некоторые другие примеры
В завершение обзора рассмотрим немного экзотики. Будет ли ошибка в результате выполнения следующего запроса:

Если вы решили, что это бессмысленная запись, то вы ошибаетесь – это вполне корректное задание даты в соответствии со стандартом ANSI, запрос №20 выполнится успешно и вернет 25.12.1928.

Какой из запросов не выполнится?

Данный пример призван продемонстрировать наличие третьего параметра в функции to_date. Данный параметр позволяет установить значение одного из NLS (National Language Support) параметров только для этого вызова функции to_date. Установку NLS параметров для сессии мы рассматривали в примере №5. Ключевая разница запросов №20 и №21 состоит не в названии месяца (маска MON автоматически замещается маской MONTH, как это описано в примере №5), а в указании разных языков даты. Запрос №21 ожидает название месяца на английском и, соответственно, не выполнится, запрос №22 ожидает название месяца на русском и выполнится успешно.

В каком случае следующее объявление таблицы может приводить к возникновению ошибок при вставке данных?

Каждый раз, когда сессия с форматом даты по умолчанию ‘DD.MON.RR’ будет производить вставку с указанием только значения первого столбца будет возникать ошибка.

Особенности отображения дат в различных приложениях

Что влияет на отображение даты
Этот раздел добавлен после публикации статьи благодаря рекомендациям, указанным в комментариях. Описанное далее верно как для отображения дат, так и для отображения чисел. Возможно, при выполнении некоторых приведенных выше в статье примеров, вы получили даты в отличном от указанного в результатах формате. Если настройки вашей сессии соответствовали указанным в примерах, то это представляется, по меньшей мере, странным.

Правда заключается в том, что при выполнении запроса
вы получаете дату, но для отображения результата на экран конкретная утилита, с помощью которой вы обращаетесь к БД, должна провести конвертацию даты в строку. Таким образом, для отображения дат (и чисел) неявным образом вызывается to_char, т.е. имеем классический случай неявной конвертации (это конвертация только для вывода на экран, ее результаты не участвуют ни в каких вычислениях и ни на что кроме отображения данных не влияют). Если есть неявная конвертация, значит, существует и маска, по которой она выполняется. В классическом случае это должна быть маска, установленная для сессии, т.е. маска, указанная в параметре NLS_DATE_FORMAT таблицы nls_session_parameters, с которой мы активно работали на протяжении всей статьи.

Давайте проверим работу некоторых приложений. Проверять будем с помощью следующего скрипта:

Проверим, какие параметры для отображения дат использует sqlplus.

Рис. 1. Результат выполнения запроса №25 в sqlplus.

Как видно из рис.1, формат отображения даты меняется в зависимости от настроек сессии, т.е. sqlplus использует настройки сессии. Это упрощает понимание процесса преобразования дат в строки и обратно, поскольку и для преобразования и для отображения используются одинаковые маски.

Некоторые продвинутые средства разработки используют свои собственные NLS настройки, не имеющие отношения к настройкам Оракл. В качестве примера проверим, какие параметры для отображения дат использует PL/SQL Developer. Для этого выполним в нем запрос №25.

Рис. 2. Результат выполнения запроса №25 в PL/SQL Developer.

Как видно из рис.2, формат отображения даты не меняется при изменении настроек сессии. Более того, если посмотреть внимательно, то видно, что и первый и второй результаты вывода даты на экран не соответствовали параметрам сессии (в первом случае выведенная дата имела год в четырехзначном формате, а маска указывала год в двухзначном формате). Это означает, что утилита использует собственные NLS настройки, в случае PL/SQL Developer’а их расположение указано на рис. 3.

Рис. 3. Установка NLS параметров отображения дат в PL/SQL Developer.

Чем могут быть вредны NLS настройки утилит
Отображение даты в формате отличном от формата сессии вредно по одной причине – оно вводит пользователя в заблуждение и может привести к возникновению ошибок. Выполним в sqlplus и PL/SQL Developer следующий запрос:
В последнюю строку запроса вместо ХХХХХХХХ мы будем вставлять полученные из предыдущей строки данные.

Результаты выполнения запроса представлены на рисунках ниже.

Рис. 4. Результат выполнения запроса №26 в sqlplus.

Рис. 5. Результат выполнения запроса №26 в PL/SQL Developer.

Почему в sqlplus выведенные на экран данные были успешно конвертированы в дату, а данные выведенные на экран PL/SQL Developer’ом не смогли сконвертироваться? Потому что для конвертации Оракл использует формат данных указанный в сессии, а данные выведенные PL/SQL Developer’ом были приведены для отображения в свой формат, отличный от формата сессии.

Заключение

В качестве заключения хочу напомнить, что почти в каждом своем посте посвященном работе с датами, Том Кайт говорит о необходимости использования явных преобразований и обязательном указании маски. «При конвертации строки в дату никогда не полагайтесь на формат даты по умолчанию, всегда явно задавайте маску» — примерно так звучат его слова. Дополнительные примеры и возможные ошибки при работе с преобразованием дат вы можете найти, воспользовавшись страничкой ask Tom.

Поскольку работа с датами заняла всю статью, то «за бортом» осталось множество интересных вопросов, которые я хотел бы рассмотреть. Скорее всего, появится и третья часть статьи, как только у меня найдется свободное время.

Источник