Введение в Oracle 10g
Часть 6
Функция MONTHS_BETWEEN(d1, d2)
Форматные маски, допустимые для функций TRUNC и ROUND
Функция TO_DATE(str[,mask [,nls_lang]])
Форматные маски, допустимые для функций TO_CHAR в случае форматирования даты
Введение
В предыдущей статье мы рассмотрели встроенные функции для работы со строками. В данной статье речь пойдет о функциях работы с датой/временем и функциями предобразования типов для даты. Для хранения даты и времени в Oracle предусмотрен специальный тип DATE. С физической точки зрения это дробное число, целая часть которого хранит количество дней с некоторой базовой даты, а дробная — время. Это позволяет совершать над датами арифметические операции — сложение и вычитание.
Функция SYSDATE
Это одна из самых часто употребляемых функций, она возвращает текущую дату и время по часам сервера.
Пример:
SELECT SYSDATE
FROM dual
SYSDATE |
26.09.2006 16:24:43 |
Функция ADD_MONTHS(d, x)
Возвращает дату, полученную в результате прибавления к дате d одного или нескольких месяцев. Количество месяцев задается параметров x, причем x может быть отрицательным — в этом случае указанное количество месяцев вычитается из заданной даты.
Пример:
SELECT SYSDATE d,
ADD_MONTHS(SYSDATE, 3) d1,
ADD_MONTHS(SYSDATE, -3) d2
FROM dual
D |
D1 |
D2 |
26.09.2006 16:35:28 |
26.12.2006 16:35:28 |
26.06.2006 16:35:28 |
Функция LAST_DAY(d)
Возвращает последнее число месяца, указанного в дате d.
Пример:
SELECT SYSDATE d,
LAST_DAY(SYSDATE) d1
FROM dual
D |
D1 |
26.09.2006 16:37:09 |
30.09.2006 16:37:09 |
Данная функция очень удобна для определения количества дней в заданном месяце, например:
SELECT SYSDATE d,
TO_CHAR(LAST_DAY(SYSDATE), 'DD') d1
FROM dual
|
D |
D1 |
26.09.2006 16:38:20 |
30 |
Функция 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
FROM dual
D1 |
D2 |
D3 |
4 |
4,32258064516129 |
-4,32258064516129 |
Функция TRUNC(d[,mask])
Производит усечение указанной даты в соответствии с маской. Если маска не указана, то усечение производится до даты (время отбрасывается).
Пример:
SELECT SYSDATE d1,
TRUNC(SYSDATE) d2
FROM dual
|
D1 |
D2 |
26.09.2006 16:45:26 |
26.09.2006 |
Рассмотрим типовые примеры — усечение даты до часов, дней, месяца и года. Форматная маска по умолчанию равна «DD»
SELECT SYSDATE d1,
TRUNC(SYSDATE, 'HH24') d2,
TRUNC(SYSDATE, 'DD') d3,
TRUNC(SYSDATE, 'MM') d4,
TRUNC(SYSDATE, 'YYYY') d5
FROM dual
|
D1 |
D2 |
D3 |
D4 |
D5 |
26.09.2006 16:49:21 |
26.09.2006 16:00:00 |
26.09.2006 |
01.09.2006 |
01.01.2006 |
Функция ROUND(d[,mask])
Функция ROUND аналогична TRUNC, но вместо усечения она производит округление. Форматная маска по умолчанию равна «DD».
Пример:
SELECT SYSDATE d1,
ROUND(SYSDATE) d2,
ROUND(SYSDATE, 'HH24') d3,
ROUND(SYSDATE, 'DD') d4,
ROUND(SYSDATE, 'MM') d5,
ROUND(SYSDATE, 'YYYY') d6
FROM dual
|
D1 |
D2 |
D3 |
D4 |
D5 |
D6 |
26.09.2006 16:50:50 |
27.09.2006 |
26.09.2006 17:00:00 |
27.09.2006 |
01.10.2006 |
01.01.2007 |
Форматные маски, допустимые для функций TRUNC и ROUND
Рассмотрим подробнее форматные маски и особенности их применения.
|
Маска |
Назначение |
СС |
Первый день столетия |
YEAR, или YYYY, или YY, или Y |
Первый день года |
Q |
Первый день квартала |
MONTH, или MON, или MM |
Первый день месяца |
WW |
Тот же день недели, что и первый день текущего года |
W |
Тот же день недели, что и первый день текущего месяца |
DDD или DD |
День |
DAY, или DY, или D |
Первый день недели |
HH, или HH12, или HH24 |
Час |
MI |
Минута |
Функция TO_DATE(str[,mask [,nls_lang]])
Функция TO_DATE преобразует строку str в дату. Преобразование ведется по маске mask, если она указана. Если маска не указана, то берется маска по умолчанию. В случае указания маски можно указать еще один параметр — язык, используемый при форматировании названий месяцев и дней. В случае ошибки анализа строки str в соответствии с заданной маской возникает исключительная ситуация. Наиболее распространенная ошибка «ORA-01830: шаблон формата даты завершается перед преобразованием всей строки ввода». Кроме того, нередко встречается ошибка «ORA-01821: формат даты не распознан» — она возникает при указании недопустимой форматной маски.
Пример:
SELECT TO_DATE('12.09.2006') d
FROM dual
|
D |
12.09.2006 |
Функция TO_CHAR(d[,mask])
Преобразует дату d в символьную строку в соответствии с заданной маской. В случае указания недопустимой маски возникает исключительная ситуация «ORA-01821: формат даты не распознан».
Пример:
SELECT SYSDATE d1,
TO_CHAR(SYSDATE, 'DD.MM.YY HH24:MI') d2
FROM dual
|
D1 |
D2 |
26.09.2006 17:09:08 |
26.09.06 17:09 |
Форматные маски, допустимые для функций TO_CHAR в случае форматирования даты
Маска |
Назначение |
||||||||||
СС |
Столетие. Пример:
SELECT SYSDATE d1, TO_CHAR(SYSDATE, 'CC') d2 FROM dual
|
||||||||||
SСС |
Столетие, причем перед датами до нашей эры ставится знак «минус». Пример:
SELECT SYSDATE d1, TO_CHAR(SYSDATE-1000000, 'SCC') d2 FROM dual
|
||||||||||
YYYY |
Год. Пример:
SELECT SYSDATE d1, TO_CHAR(SYSDATE, 'YYYY') d2 FROM dual
|
||||||||||
SYYYY |
Аналогично YYYY, но перед датами до нашей эры ставится знак «минус» |
||||||||||
YYY, YY, Y |
Аналогичны YYYY, но возвращаются соответственно последние 3,2 или 1 цифра года. Пример:
SELECT SYSDATE d1, TO_CHAR(SYSDATE, 'YYYY') d2, TO_CHAR(SYSDATE, 'YYY') d3, TO_CHAR(SYSDATE, 'YY') d4, TO_CHAR(SYSDATE, 'Y') d5 FROM dual
|
||||||||||
SYEAR и YEAR |
Год, записанный прописью c учетом текущего национального языка. Пример:
SELECT SYSDATE d1, TO_CHAR(SYSDATE, 'YEAR') d2 FROM dual
|
||||||||||
Q |
Номер квартала. Пример:
SELECT SYSDATE d1, TO_CHAR(SYSDATE, 'Q') d2 FROM dual
|
||||||||||
MM |
Месяц. Пример:
SELECT SYSDATE d1, TO_CHAR(SYSDATE, 'MM') d2 FROM dual
|
||||||||||
RM |
Номер месяца, записанный с имитацией римских цифр при помощи символов X и I. Пример:
SELECT SYSDATE d1, TO_CHAR(SYSDATE, 'RM') d2 FROM dual
|
||||||||||
MONTH |
Название месяца прописью. Пример:
SELECT SYSDATE d1, TO_CHAR(SYSDATE, 'MONTH') d2 FROM dual
|
||||||||||
MON |
Название месяца прописью в сокращенном до трех символов виде. Пример:
SELECT SYSDATE d1, TO_CHAR(SYSDATE, 'MON') d2 FROM dual
|
||||||||||
WW |
Неделя года. Пример:
SELECT SYSDATE d1, TO_CHAR(SYSDATE, 'WW') d2 FROM dual
|
||||||||||
W |
Неделя месяца. Пример:
SELECT SYSDATE d1, TO_CHAR(SYSDATE, 'W') d2 FROM dual
|
||||||||||
DDD |
День года. Пример:
SELECT SYSDATE d1, TO_CHAR(SYSDATE, 'DDD') d2 FROM dual
|
||||||||||
DD |
День месяца. Пример:
SELECT SYSDATE d1, TO_CHAR(SYSDATE, 'DD') d2 FROM dual
|
||||||||||
D |
День недели. Пример:
SELECT SYSDATE d1, TO_CHAR(SYSDATE, 'D') d2 FROM dual
|
||||||||||
DAY |
День недели прописью, при необходимости дополняется до девяти символов пробелами. Пример:
SELECT SYSDATE d1, TO_CHAR(SYSDATE, 'DAY') d2 FROM dual
|
||||||||||
DY |
День недели прописью, сокращенный до трех символов. Пример:
SELECT SYSDATE d1, TO_CHAR(SYSDATE, 'DY') d2 FROM dual
|
||||||||||
J |
Дата юлианского календаря. Является числом дней от 1.01.4712 до нашей эры. Пример:
SELECT SYSDATE d1, TO_CHAR(SYSDATE, 'J') d2 FROM dual
|
||||||||||
HH24 |
Час дня по 24-часовой шкале (0-23). Пример:
SELECT SYSDATE d1, TO_CHAR(SYSDATE, 'HH24') d2 FROM dual
|
||||||||||
HH12 или HH |
Час дня по 12-часовой шкале (1-12). Пример:
SELECT SYSDATE d1, TO_CHAR(SYSDATE, 'HH') d2 FROM dual
|
||||||||||
AM или PM |
Вывод признака «до полудня» — AM и «после полудня» — PM. Указание AM и PM в форматной маске равнозначно. Данная маска часто употребляется совместно с HH/HH12. Пример:
SELECT SYSDATE d1, TO_CHAR(SYSDATE, 'HH AM') d2 FROM dual
|
||||||||||
MI |
Минуты (0-59). Пример:
SELECT SYSDATE d1, TO_CHAR(SYSDATE, 'MI') d2 FROM dual
|
||||||||||
SS |
Секунды (0-59). Пример:
SELECT SYSDATE d1, TO_CHAR(SYSDATE, 'SS') d2 FROM dual
|
||||||||||
SSSSS |
Количество секунд после полуночи. Пример:
SELECT SYSDATE d1, TO_CHAR(SYSDATE, 'SSSSS') d2 FROM dual
Данная форматная маска полезна для измерения временных интервалов в секундах. |
||||||||||
-/.,:; |
Знаки пунктуации. Они выводятся в соответствующие места отформатированной даты. Пример:
SELECT SYSDATE d1, TO_CHAR(SYSDATE, 'DD.MM.YYYY HH24.MI') d2, TO_CHAR(SYSDATE, 'DD/MM/YYYY HH12.MI PM') d3 FROM dual
|
||||||||||
SP |
Форматный суффикс. Его добавление к элементу форматной маски, возвращающему число, приводит к форматированию этого числа прописью. Пример:
SELECT SYSDATE d1, TO_CHAR(SYSDATE, 'DDSP') d2 FROM dual
|
Элементы форматной маски, предназначенные для вывода текстовой информации, чувствительны к регистру — регистр управляет регистром форматируемого текста. Рассмотрим управление регистром на примере форматной маски DAY:
SELECT SYSDATE d1,
TO_CHAR(SYSDATE, 'DAY') d2,
TO_CHAR(SYSDATE, 'Day') d3,
TO_CHAR(SYSDATE, 'day') d4
FROM dual
|
D1 |
D2 |
D3 |
D4 |
26.09.2006 17:47:45 |
ВТОРНИК |
Вторник |
вторник |
Несложно заметить, что если маска записана в верхнем регистре, то и форматируемое текстовое значение возвращается в верхнем регистре. Если в нижнем — форматируемое значение тоже будет в нижнем. И наконец, запись маски с прописной буквы приводит к форматированию формируемого текста таким образом, чтобы он начинался с прописной буквы.
Заключение
В данной статье мы рассмотрели все основные встроенные функции Oracle, предназначенные для работы с датами и выполнения операций преобразования типов с типом «дата». В следующей статье мы рассмотрим функции преобразования и форматирования чисел и перейдем к рассмотрению практических запросов, использующих рассмотренные ранее функции.
 |
|
