SELECT SYSTIMESTAMP
FROM DUAL;
Функции для работы с данными, имеющими тип даты и времени
Для обработки данных, имеющих тип Date, можно использовать функции, представленные в таблице 3.4.
Таблица 3.4. Функции для работы с данными, имеющими тип даты и времени
Значения, имеющие этот тип, могут участвовать в арифметических операциях с некоторыми ограничениями. Например, разница меду двумя датами равна количеству дней, прошедших между этими датами, но нельзя непосредственно складывать значения, имеющие тип Date.
Прибавление целого значения n к значению типа Date эквивалентно прибавлению n дней к дате. Прибавление значения n/24 к значению типа Date эквивалентно прибавлению n часов к дате.
Если в выражении участвует строка, содержащая значение даты, то ее рекомендуется преобразовать к значению типа Date, используя функцию TO_DATE ().
Рассмотрим примеры, в которых значения, имеющие тип Date, участвуют в арифметических выражениях.
Пример 3.23. Вывод значения текущей даты, увеличенного на 45 дней
SELECT SYSDATE, SYSDATE +45
FROM DUAL;
Пример 3.24. Вывод значения текущей даты и времени, увеличенного на два часа
SELECT TO_CHAR (SYSDATE, ′DD MONTH YYYY, HH24:MI: SS′)
As Date1,
TO_CHAR (SYSDATE +2/24, ′DD MONTH YYYY, HH24:MI: SS′)
As Date2
FROM DUAL;
Пример 3.25. Определить количество дней, прошедших между датой приема на работу и сегодняшним днем
SELECT employee_id, ROUND (SYSDATE – hire_date AS DAYS, -2)
FROM Employees;
Следует обратить внимание на то, что этот запрос возвращает дробное значение, так как значение, возвращаемое функцией SYSDATE, содержит текущее время, которое трактуется как часть суток: например, 12 часов отобразятся в виде значения 0,5.
Пример 3.26. Определить количество недель, прошедших между датой приема на работу и сегодняшним днем
SELECT employee_id, TRUNC ((SYSDATE – hire_date) /7)
AS WEEKS
FROM Employees;
Для определения интервалов между двумя датами в месяцах следует использовать специальную функцию MONTHS_BETWEEN.
Пример 3.27. Вывести данные о сотрудниках и количестве месяцев, прошедших между датой приема на работу и сегодняшним днем
SELECT employee_id, first_name, last_name,
department_id, hire_date,
—
TRUNC (MONTHS_BETWEEN (SYSDATE, hire_date)) AS MONTHS
– —
FROM Employees;
Пример 3.28. Вывести данные о сотрудниках, которые проработали более 30 лет
SELECT employee_id, first_name, last_name, salary,
department_id, hire_date
FROM Employees
WHERE MONTHS_BETWEEN (SYSDATE, hire_date)> 360;
Функция NEXT_DAY (x, день недели) возвращает следующую ближайшую дату, соответствующую определенному дню недели: например, среда.
Пример 3.29. Использование функции NEXT_DAY
SELECT SYSDATE AS «Сегодня», EXT_DAY (SYSDATE, ′Tuesday′)
AS Tuesday
FROM DUAL;
Функция LAST_DAY (x) возвращает дату, соответствующую последнему дню месяца, которому принадлежит x.
Пример 3.30. Использование функции LAST_DAY
SELECT SYSDATE, LAST_DAY (SYSDATE)
FROM DUAL;
Функция ROUND (x, {параметр}) округляет дату x, если параметр отсутствует, то до начала ближайших суток; если {параметр} = MM/ MON / MONTH – то до начала ближайшего месяца; если параметр = YY / YYYY /YEAR – то до начала ближайшего года.
Пример 3.31. Использование функции ROUND c параметром MM
SELECT ROUND (TO_DATE (′12.05.2018′, ′DD.MM.YYYY′),′MM′)
As ′′ ROUND MONTCH 12.05.2018 ′′,
ROUND (TO_DATE (′20.05.2018′, ′DD.MM.YYYY′),′MM′)
As ′′ ROUND MONTCH 20.05.2018 ′′
FROM DUAL;
Пример 3.32. Использование функции ROUND c параметром YYYY
SELECT ROUND (TO_DATE (′12.05.2018′, ′DD.MM.YYYY′),′YYYY′)
As ′′ ROUND YEAR 12.05.2018 ′′,
ROUND(TO_DATE(′12.07.2018′,′DD.MM.YYYY′),′YYYY′)
As ′′ ROUND YEAR 12.07.2018 ′′
FROM Dual;
Функция TRUNC (x, {параметр}) отличается от ROUND тем, что возвращает начало текущих суток, начало текущего месяца, начало текущего года соответственно.
Пример 3.33. Использование функции TRUNC c параметром MM.
SELECT TRUNC (TO_DATE (′12.05.2018′, ′D.MM.YYYY′),′MM′)
As ′′ TRUNC MONTCH 12.05.2018 ′′,
TRUNC (TO_DATE (′20.05.2018′, ′D.MM.YYYY′),′MM′)
As ′′ TRUNC MONTCH 20.05.2018 ′′
FROM DUAL;
Функция EXTRACT
Функция EXTRACT возвращает значение заданного поля даты-времени из значения, имеющего тип date. Синтаксис:
EXTRACT ({часть даты} FROM {дата})
Таблица 3.5. Часть даты, возвращаемая функцией EXTRACT
Пример 3.34. Вывести данные о сотрудниках, которые были приняты на работу в 1999 году
SELECT employee_id, first_name, last_name, department_id, hire_date
FROM Employees
WHERE EXTRACT (YEAR FROM hire_date) =1999;
Функции конвертирования
В СУБД Oracle используются три простых типа данных:
– строки CHAR, VARCHAR2;
– числа NUMBER;
– даты DATE.
Сервер Oracle может конвертировать данные, имеющие тип VARCHAR2 и CHAR, в данные типов NUMBER и DATE. Он может преобразовать данные, имеющие тип NUMBER или DATE, в данные типов CHAR и VARCHAR2.
Преобразование может осуществляться явным и неявным образом. Неявное преобразование осуществляется при выполнении следующего оператора:
{столбец} тип А = {начение/выражение} тип Б
При выполнении этого оператора значение или выражение в правой части преобразуется к типу, который имеет левая часть.
– WHERE order_date= ′2-04-2017′;
– WHERE order_date= ′26-apr-2017′;
– WHERE order_date= ′26-апр-2017′;
В этих примерах в зависимости от языковых настроек во втором или третьем операторе возникнет ошибка. Если используемый язык – английский, то ошибка возникнет в третьем операторе, Если используемый язык – русский, то ошибка возникнет во втором операторе. Следует иметь в виду, что значение ′26-APR-2017′ имеет тип строки символов.
– WHERE salary = ′4200′;
– WHERE salary = ′4000′+200;
– WHERE salary = ′4.200′;
– WHERE salary = ′$4200′;
В этих примерах первый и второй операторы будут успешно выполнены, при выполнении третьего и четвертого операторов возникнут ошибки, так как эти строки содержат недопустимые символы.
Для того чтобы неявное преобразование было возможно, необходимо, чтобы присваиваемое значение соответствовало формату столбца, которому это значение присваивается.
Хотя неявное преобразование возможно, лучше для этого использовать специальные функции. Чаще всего функции преобразования типов используются для того, чтобы числовые данные и даты отобразить в наиболее удобном (понятном) виде.
Четыре типа преобразования:
– число в строку символов;
– строку символов в число;
– дату в строку символов;
– строку символов в дату.
Преобразование чисел в строку символов
Числа, хранящиеся в базе данных, не имеют форматирования. Это означает, что они не имеют символов валюты, запятых, десятичных знаков и других параметров форматирования. Чтобы добавить форматирование, необходимо преобразовать число в строку символов. Для этого используется функция:
TO_CHAR (Х {маска преобразования})
Для преобразования численного значения в строку можно использовать элементы формата, представленные в таблице 3.6.
Таблица 3.6. Элементы маски преобразования, используемые в функции TO_CHAR
Пример 3.35. Использование функции TO_CHAR
SELECT TO_CHAR (1475.29, ′9999.9′) As ′′9999.9′′,
TO_CHAR (1475.29, ′9999.99′) As ′′9999.99′′,
TO_CHAR (1475.29, ′099999.90′) As ′′099999.99′′,
TO_CHAR (1475.29, ′9,999.99′) As ′′9,999.99′′,
TO_CHAR (1475.29, ′$9,999.99′) As ′′$9,999.99′′
FROM DUAL;
Преобразование строки символов в число
Для преобразования символьного значения в число используется функция TO_NUMBER. Синтаксис:
TO_NUMBER (х, {маска преобразования})
Строка x может содержать цифры и символы, которые соответствуют заданному формату. Параметр {маска преобразования} определяет, как нужно интерпретировать символьное представление числа, может содержать те же элементы, которые были определены для функции TO_CHAR.
Если число символов в строке будет больше числа элементов формата, то возникает ошибка. Примеры преобразований, при которых возникает ошибка: