Перевод строки в дату sql
Когда мы присваиваем значение одного одного типа столбцу, который хранит данные другого типа, либо выполняем операции, которые вовлекают данные разных типов, SQL Server пытается выполнить преобразование и привести используемое значение к нужному типу. Но не все преобразования SQL Server может выполнить автоматически. SQL Server может выполнять неявные преобразования от типа с меньшим приоритетом к типу с большим приоритетом. Таблица приоритетов (чем выше, тем больший приоритет):
| datetime |
| smalldatetime |
| float |
| real |
| decimal |
| money |
| smallmoney |
| int |
| smallint |
| tinyint |
| bit |
| nvarchar |
| nchar |
| varchar |
| char |
То есть SQL Server автоматически может преобразовать число 100.0 (float) в дату и время (datetime).
Функция CAST преобразует выражение одного типа к другому. Она имеет следующую форму:
Для примера возьмем следующие таблицы:
Например, при выводе информации о заказах преобразует числовое значение и дату в строку:
Convert
Третий необязательный параметр задает стиль форматирования данных. Этот параметр представляет числовое значение, которое для разных типов данных имеет разную интерпретацию. Например, некоторые значения для форматирования дат и времени:
Некоторые значения для форматирования данных типа money в строку:
Например, выведем дату и стоимость заказов с форматированием:
TRY_CONVERT
При использовании функций CAST и CONVERT SQL Server выбрасывает исключение, если данные нельзя привести к определенному типу. Например:
Дополнительные функции
Кроме CAST, CONVERT, TRY_CONVERT есть еще ряд функций, которые могут использоваться для преобразования в ряд типов:
CHAR(int) : преобразует числовой код ASCII в символ. Нередко используется для тех ситуаций, когда необходим символ, который нельзя ввести с клавиатуры
ASCII(char) : преобразует символ в числовой код ASCII
NCHAR(int) : преобразует числовой код UNICODE в символ
UNICODE(char) : преобразует символ в числовой код UNICODE
Преобразование даты и времени в PL/SQL
Теперь, когда вы имеете представление о типах данных Oracle, предназначенных для работы с датой и временем, давайте посмотрим, как присваивать даты значениям переменных этих типов и как использовать такие значения в дальнейшем. Дата и время в понятной для человека форме представляются в виде символьных строк наподобие «5 марта 2009 года» и «10:30», так что речь пойдет о преобразовании значений даты/ времени в PL/SQL — приведении символьных строк в соответствие с внутренним представлением Oracle, и наоборот.
PL/SQL проверяет и хранит даты от 1 января 4712 года до н. э. до 31 декабря 9999 года н. э. (В документации Oracle указана максимальная дата 31 декабря 4712 года). Если ввести дату без времени (во многих приложениях не требуется отслеживать время, поэтому PL/SQL позволяет его не задавать), то время в таком значении по умолчанию будет равно полуночи (00:00:00).
База данных Oracle способна интерпретировать практически все известные форматы даты и времени. Эта возможность основана на использовании маски форматирования даты, которая представляет собой строку специальных символов, определяющих формат даты для Oracle.
В следующем разделе мы рассмотрим несколько разных масок форматирования даты. Полный список всех элементов форматирования масок приводится в приложении В.
Преобразование строк в даты
Первой практической задачей, с которой вы столкнетесь при работе с датами, будет присваивание даты (или времени) переменной PL/SQL. Операция выполняется путем преобразования даты/времени во внутренний формат базы данных. Такие преобразования выполняются либо неявно, присваиванием символьной строки переменной типа даты/времени, либо явно, с помощью встроенных функций Oracle.
Неявное преобразование — вещь рискованная, и мы не рекомендуем на него полагаться. Пример неявного преобразования символьной строки при ее присваивании переменной типа DATE :
PL/SQL поддерживает следующие функции преобразования строк в дату и время:
Эти функции не только ясно показывают, что в коде выполняется преобразование, но и позволяют точно задать используемый формат даты/времени.
Вторая версия TO_DATE может использоваться только с маской форматирования J (Юлианская дата, то есть количество дней, прошедших с 1 января 4712 года до н. э.). Только в этом варианте использования TO_DATE в первом параметре может передаваться число.
В остальных случаях параметры имеют следующий смысл:
Здесь язык — один из языков, распознаваемых вашим экземпляром базы данных. За информацией о допустимых языках обращайтесь к руководству Oracle Globalization Support Guide.
Ошибки Oracle из диапазона от ORA-01800 до ORA-01899 связаны с преобразованиями дат. Чтобы узнать о некоторых тонкостях обработки дат, просмотрите документацию по этим ошибкам и ознакомьтесь с описаниями их причин. Вот лишь некоторые из них:
Как показывают предыдущие примеры, функция TO_TIMESTAMP_TZ может преобразовывать символьные строки с информацией часового пояса. И хотя часовые пояса на первый взгляд выглядят просто, в действительности все намного сложнее — вы убедитесь в этом в разделе «Часовые пояса».
Преобразование даты в строку
Здесь входная_дата — символьная строка, представляющая значение даты/времени; маска — строка из одного или нескольких элементов формата; язык_nls — строка, задающая язык отображения даты. Параметры маска и язык_nls не являются обязательными.
Несколько примеров использования функции TO_CHAR для преобразования даты:
Обратите внимание на выполненное округление. Во входных данных было указано количество секунд 00.123456789. Значение было округлено (не усечено, а именно округлено) до шести цифр: 00,123457.
Сообщение об ошибке (формат даты не распознается) только сбивает с толку. Формат даты абсолютно нормален — проблема в том, что он применяется не к тому типу данных. Поэтому при получении такого сообщения следует проверить не только формат даты, но и тип данных, который вы собираетесь преобразовывать.
Часовые пояса
Рассмотрим несколько примеров. Начнем с простейшего случая, в котором часовой пояс вообще не указан:
Дата или время?
Учтите, что каждое значение даты/времени содержит как дату, так и время. Стоит вам забыть об этом, и в вашем коде появятся ошибки. Рассмотрим пример: допустим, я написал код PL/SQL, который должен быть выполнен 1 января 2015 года:
Теперь в обеих сторонах сравнения указывается время суток, но этим временем является полночь. Функция TO_DATE также возвращает время суток, которое по умолчанию соответствует полуночи (то есть 00:00:00). Итак, в какое бы время 1 января 2015 года ни был выполнен этот блок, сравнение будет выполнено успешно, и процедура Apply2009PriceChange будет выполнена.
Функцию TRUNCATE также удобно использовать для удаления времени из временных штампов.
В этом примере значение даты/времени интерпретируется как Восточное стандартное время США (независимо от часового пояса сеанса).
Этот пример интересен тем, что он представляет не Восточное стандартное время, а просто Восточное время. Разница между ними заключается в том, что термин Восточное время может обозначать как Восточное стандартное время, так и Восточное летнее время — в зависимости от того, действует ли переход на летнее время. Я специально сформулировал этот пример так, чтобы он был неоднозначным. 2 ноября 2014 года — это дата завершения действия Восточного летнего времени, когда время 2:00 превращается в 1:00. Поэтому в указанный день 1:30 наступает дважды: по Восточному летнему и по Восточному стандартному времени. Так какое же время имеется в виду при определении 1:30 2 ноября 2014 года?
Названия региона недостаточно для различения стандартного и летнего времени. Для устранения этой неоднозначности необходимо задать сокращение часового пояса, как это сделано в двух следующих примерах. Восточное летнее время обозначается сокращением EDT (Eastern Daylight Time):
Если параметр сеанса ERROR_ON_OVERLAP_TIME равен TRUE (по умолчанию используется значение FALSE ), база данных будет выдавать ошибку при задании неоднозначного времени.
Восточное стандартное время обозначается сокращением EST (Eastern Standard Time):
Для предотвращения неоднозначности рекомендуется либо задавать смещение часового пояса в часах и минутах (например, –5:00), либо использовать сочетание названия и сокращения часового пояса. Если указано только имя региона, то при наличии неоднозначности в определении времени (летнее или стандартное) Oracle будет считать, что задано стандартное время.
О СТАНДАРТЕ ЧАСОВЫХ ПОЯСОВ
Часовые пояса настолько важны, что, казалось бы, должен существовать некий международный стандарт, определяющий их названия и сокращения. Тем не менее такого стандарта нет. Названия часовых поясов и сокращения не только не стандартизированы, но среди них даже встречаются повторения. Например, сокращение EST обозначает Восточное стандартное время и в США, и в Австралии, то есть соответствует двум часовым поясам с разным смещением времени. Сокращение BST применяется для нескольких часовых поясов, включая Тихоокеанский/Мидуэй и Лондонский, отличающиеся на 12 часов в режиме летнего времени или на 11 часов в остальное время года. Вот почему функция TO_TIMESTAMP не позволяет задавать часовой пояс лишь по сокращению названия.
Точное совпадение маски форматирования
При преобразовании символьной строки в дату/время функции преобразования TO_ *обычно руководствуются несколькими предположениями:
С модификатором FX какая-либо гибкость в интерпретации строки полностью отсутствует. Строка не может содержать лишние пробелы, если их нет в модели. Ее числовые значения должны включать начальные нули, если модель форматирования включает дополнительные цифры. Наконец, знаки препинания и литералы должны точно соответствовать знакам препинания и заключенному в кавычки тексту маски форматирования (не считая регистра символов, который всегда игнорируется). Все эти правила продемонстрированы в следующих примерах:
PL/SQL выдает одну из следующих ошибок:
Однако следующий пример выполняется успешно, потому что регистр символов не учитывается, и модификатор FX этого факта не меняет:
Модификатор FX может задаваться в верхнем регистре, нижнем регистре или со смешением регистров; его действие от этого не изменяется.
Модификатор FX работает как переключатель и может многократно встречаться в маске форматирования. Пример:
Каждый раз, когда модификатор FX встречается в маске форматирования, происходит переключение. В приведенном примере точное совпадение обязательно для дня и года, но не для месяца.
Ослабление требований к точности совпадения
Интерпретация года из двух цифр
Переход в новое тысячелетие породил интерес к хранению года в формате из четырех цифр, потому что разработчики внезапно осознали неоднозначность часто встречающегося формата с двумя цифрами. Например, к какому году относится дата 1-Jan- 45 — к 1945 или 2045? В таких ситуациях лучше всего использовать однозначный год с четырьмя цифрами. Но несмотря на это понимание, старые привычки изменяются с трудом, а внесение изменений в существующие системы сопряжено с большими трудностями. Возможно, ваши пользователи предпочитают вводить год из двух цифр, а не из четырех. Для подобных случаев Oracle предоставляет форматный элемент RR, интерпретирующий год из двух цифр в скользящем окне.
Если текущий год относится к первой половине века (годы с 0 по 49), то:
Если же текущий год относится ко второй половине века (годы с 50 по 99), то:
Запутались? Я тоже разобрался не сразу. Правила RR пытаются предположить, какой век подразумевал пользователь, если он не был указан явно.
Логику RR в текущих приложениях можно активизировать несколькими способами. Самый простой и элегантный способ — изменение маски форматирования дат по умолчанию в экземпляре базы данных. Собственно, Oracle уже делает это за нас. В стандартной установке Oracle параметр NLS_DATE_FORMAT задается следующим образом:
Если маска форматирования дат не будет жестко запрограммирована на других экранах или отчетах, год из двух цифр будет интерпретироваться по правилам, приведенным выше.
Преобразование часовых поясов в символьные строки
Часовые пояса усложняют преобразование значений даты/времени в символьные строки. Информация часового пояса состоит из следующих элементов.
Все эти элементы хранятся отдельно в переменной TIMESTAMP WITH TIME ZONE. Смещение относительно UTC присутствует всегда, но возможность вывода названия региона или сокращения зависит от того, задана эта информация или нет. Рассмотрим следующий пример:
В этом коде следует обратить внимание на некоторые аспекты, относящиеся к информации часовых поясов:
Внутреннее преобразование переводит его в смещение относительно UTC, но имя региона при этом сохраняется. Таким образом, для ts2 может быть выведено как смещение UTC, так и имя региона.
Смещения относительно UTC и регионы часовых поясов связаны отношениями «один ко многим»; смещения недостаточно для однозначного определения имени региона. Вот почему невозможно вывести имя региона, если оно не было задано изначально.
Дополнение вывода с модификатором FM
По умолчанию следующая маска форматирования генерирует как дополняющие пробелы, так и начальные нули (название месяца отделяется от дня пятью пробелами):
Однако с модификатором FM в начале маски и лишний пробел, и начальные нули исчезают:
Модификатор может задаваться в верхнем регистре, нижнем регистре или со смешением регистров; его действие от этого не изменяется.
Помните, что модификатор FM работает как переключатель и может многократно встречаться в маске форматирования. Каждый раз, когда он встречается в маске форматирования, происходит переключение. По умолчанию (то есть если модификатор FM вообще не встречается в маске) пробелы не подавляются, а начальные нули включаются в итоговое значение.
Функции конвертации типов данных доступные в SQL
Строчные функции конвертации типа данных разработаны для изменения типа данных столбца, выражения или литерала. Наиболее часто используемые функции конвертации это 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’ не будет, как показано в следующих примерах
Query 3: select mod(’11’, 2) from dual
Query 4: select mod(‘11.123’, 2) from dual
Query 5: select mod(‘11.123.456’, 2) from dual
Query 6: select mod(‘$11’, 2) from dual
Запросы 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_DATE(’01-JAN-00′,’DD-MON-RR’),’Day’).
Использование функций TO_CHAR, TO_NUMBER и TO_DATE
При сертификации очень часто используются вопросы о функциях TO_NUMBER, TO_DATE и TO_CHAR с примерами для анализа. Обсуждение функции TO_CHAR разделим на две части: конвертация даты в строку и конвертация числа в строку. Такое разделение основано на том что для разных типов данных доступны разные маски форматирования. Помимо этих функций существует много других, но эти используется наиболее часто.
Использование функций конвертации
Во многих ситуациях возникает потребность в использовании функций конвертации, начиная от форматирования даты в отчётах и до проверки численных значений в символьных столбцах перед стартом арифметических вычислений.
Таблица 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. Рассмотрим два запроса
Query 1: select to_char(00001)||’ is a special number’ from dual;
Query 2: select to_char(00001, ‘0999999’)||’ is a special number’ from dual;
В запросе номер один у числа 00001 убираются ведущие нули, значение преобразуется в строку ‘1’ и возвращается результат ‘1 is a special number’. Зпрос номер два использует маску форматирования ‘0999999’ для числа 00001 преобразуя число в строку ‘0000001’ и возвращая результат ‘0000001 is a special number’. 0 и 6 девяток в маске форматирования указывают функции TO_CHAR что необходимо использовать ведущие нули, и длина строки должна быть 7. Таким образом строка, возвращаемая функцией TO_CHAR содержит семь символов.
Tip Конвертация чисел в строки надёжный способ убедиться что функция и SQL запрос в целом, который ожидает символьного значения, не вернёт ошибку когда встретится число. Конвертация чисел в строки часто используется для форматирования значений для отчетов. Маска форматирования поддерживает символ валюты, разделитель порядков и разделитель целой и дробной части, что часто используется при отображении финансовой информации.
Конвертация даты в строку используя функцию TO_CHAR
Вы можете использовать преимущества модели масок форматирования при конвертации ДАТЫ в практически любой вариант отображения даты как символьного значения используя функцию TO_CHAR. Синтаксис функции TO_CHAR(date1, [format], [nls_parameter]).
Только параметр date1 обязательный; тогда он должен быть значением, которое может неявно преобразоваться в строку. Необязательный параметр format регистрозависимый и должен быть обрамлён одинарными кавычками. Маска форматирования указывает какие лементы даты должы быть выбраны и как отображать названия элементов даты: полные названия или аббревиатуры. Названия дней и месяцев автоматически разделяются пробелом. Такое поведение можно изменить, используя параметр маски fill mode (fm). Указав в начале маски параметр fm, вы укажете Oracle о необходимости убрать все пробелы. Доступно много опций для маски форматирования, часть из которых отображена в таблице 10-5. Рассмотрим три запроса
Query 1: select to_char(sysdate)||’ is today»s date’ from dual;
Query 2: select to_char(sysdate,’Month’)||’is a special time’ from dual;
Query 3: select to_char(sysdate,’fmMonth’)||’is a special time’ from dual;
Если текущая системная дата 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 с маской форматирования указанной в соответствующем столбце.
Таблица JOB_HISTORY хранит информацию о должностях сотрудника, которые он занимал в компании. Запрос на рисунке 10-3 выбирает информацию из этой таблицы о конце срока действия должности сотрудника для каждого сотрудинка на основании столбцов END_DATE, EMPLOYEE_ID и JOB_ID и форматирует результат в красивое предложение. Символьное выражение объединяется с функцией TO_CHAR в которой используется маска ‘fmDay «the «ddth «of» Month YYYY’. Параметр fm используется чтобы убрать пробелы в имени дня недели и месяца. Два литерата ограниченые двойными кавычками используются для добавления читабельности результату. Литерал “th” применяется к элементу даты чтобы сделать литературно правильным отображение даты как 17 th или 31th. ‘Month’ в маске означает использовать полное имя месяца и наконец YYYY форматирует год как все четыре символа года.
Рисунок 10-3 – Запрос в таблицу JOB_HISTORY
select ‘Employee ‘||EMPLOYEE_ID||’ quit as ‘||JOB_ID||’ on ‘||TO_CHAR(END_DATE,’fmDay «the «ddth «of» Month YYYY’) «Quitting Date»
Несмотря на то что компонент «век» не отображается по умолчанию, он хранится в базе данных и доступен для запроса. Маска форматирования DD-MON-RR используется по умолчанию для ввода значений и отображения. Когда значение добавляется или изменяется если явно неуказан век, то используется век из функции SYSDATE. Формат RR отличается от формата YY и так как RR также использует значение столетия. Влияние значения столетия на формат RR легче понять если рассмотреть следующие принципы
Конвертация строки в дату используя функцию 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 требует обязательного совпадения строки и маски форматирования. Если строка не совпадает с маской – возвращается ошибка. Рассмотрим несколько примеров
Query 1: select to_date(’25-DEC-2010′) from dual;
Query 2: select to_date(’25-DEC’) from dual;
Query 3: select to_date(’25-DEC’, ‘DD-MON’) from dual;
Query 4: select to_date(’25-DEC-2010 18:03:45′, ‘DD-MON-YYYY HH24:MI:SS’) from dual;
Query 5: select to_date(’25-DEC-10′, ‘fxDD-MON-YYYY’) from dual;
Запрос 1 преобразует строку ’25-DEC-2010’ и строка может быть преобразована в дату используя маску DD-MON-YYYY. Дефис можно заменить на другой разделитель. Так как не было указано никакой информации о времени то используется значение по-умолчанию 00:00:00. Запрос 2 не может неявно преобразовать строку в дату, так как недостаточно информации и возвращается ошибка ORA-01840: input value is not long enough for date format. Указав маску DD-MON для строки ’25-DEC’ в запросе номер три, Oracle может преобразовать значение в дату, но так как год и время не указаны, в значении года будет использоваться значение года функции SYSDATE, а время установлено в полночь. Если текущий год будет 2009, то запрос 3 вернёт дату 25/DEC/09 00:00:00. Запрос 4 преобразует строку с явно указанными всеми компнонентами даты и времени. Запрос 5 использует параметра fx. Так как год в строке указан двумя символами, а требуемый формат предполагает четыре цифры года, то запрос возвращает ошибку ORA-01862: the numeric value does not match the length of the format item.
Конвертация строки в число используя функцию TO_NUMBER
Функция TO_NUMBER возвращает значение типа данных NUMBER. Исходная строка должна быть составлена таким образом, чтобы все несовместимые символы отсутствовали или были указаны в соответствующей маске форматирования. Синтаксис функции TO_NUMBER(string1, [format], [nls_parameter]). Только string1 является обязательным параметром, и если не указан параметр format то значение должно быть таким, чтобы была возможность неявно сконвертировать его в число. Маски форматирования идентичны перечисленным в таблице 10-4. Рассмотрим запросы
Query 1: select to_number(‘$1,000.55’) from dual;
Query 2: select to_number(‘$1,000.55′,’$999,999.99’) from dual;
Запрос один не может неявно преобразовать строку, так как она содержит знак валюты и разделители, которые явно неуказаны в маске, поэтому возвращается ошибка 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