Функции CAST и CONVERT (SQL Server Compact). Sql convert описание

CONVERT ФУНКЦИЯ | Oracle PL/SQL •MySQL •SQL Server

Узнайте, как использовать Oracle / PLSQL функцию CONVERT с синтаксисом и примерами.

Описание

Функция Oracle / PLSQL CONVERT преобразует строку из одного набора символов в другой.

Синтаксис

Синтаксис функции CONVERT:

CONVERT( string1 , char_set_to , [ char_set_from ] )

Параметры или аргументы

string1 строка для преобразования.

char_set_to является набор символов, чтобы преобразовать в.

char_set_from является набор символов для преобразования из.

Примечание

Наборы символов включают в себя:

Character Set Описание

US7ASCII	US 7-bit ASCII character set
WE8DEC	West European 8-bit character set
WE8HP	HP West European Laserjet 8-bit character set
F7DEC	DEC French 7-bit character set
WE8EBCDIC500	IBM West European EBCDIC Code Page 500
WE8PC850	IBM PC Code Page 850
WE8ISO8859P1	ISO 8859-1 West European 8-bit character set

Применение

Функция CONVERT можно использовать в следующих версиях Oracle / PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

Пример

Рассмотрим несколько примеров функции CONVERT и изучим, как использовать функцию CONVERT в Oracle / PLSQL.

SQL> SELECT CONVERT('A B C D E A E I O O', 'US7ASCII', 'WE8ISO8859P1') FROM DUAL; CONVERT('ABCDEAEIOO','US7ASCII ------------------------------ A B C D E A E I O O

SQL> SELECT CONVERT('A B C D E A E I O O', 'US7ASCII', 'WE8ISO8859P1') FROM DUAL;

CONVERT('ABCDEAEIOO','US7ASCII

------------------------------

A B C D E A E I O O

oracleplsql.ru

SQL Convert()

CONVERT() -данная функция предназначена для различного вывода информации о времени.

Синтаксис

Где data_type(length) задает тип данных (с возможностью указания длины), data_to_be_converted содержится переменная, которую собственно нужно конвертировать, style для указания стиля вывода даты.

Допустимые типы:

Style IDStyle Format

100 or 0	mon dd yyyy hh:miAM (or PM)
101	mm/dd/yy
102	yy.mm.dd
103	dd/mm/yy
104	dd.mm.yy
105	dd-mm-yy
106	dd mon yy
107	Mon dd, yy
108	hh:mm:ss
109 or 9	mon dd yyyy hh:mi:ss:mmmAM (or PM)
110	mm-dd-yy
111	yy/mm/dd
112	yymmdd
113 or 13	dd mon yyyy hh:mm:ss:mmm(24h)
114	hh:mi:ss:mmm(24h)
120 or 20	yyyy-mm-dd hh:mi:ss(24h)
121 or 21	yyyy-mm-dd hh:mi:ss.mmm(24h)
126	yyyy-mm-ddThh:mm:ss.mmm(no spaces)
130	dd mon yyyy hh:mi:ss:mmmAM
131	dd/mm/yy hh:mi:ss:mmmAM

Пример

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

Результат запроса:

1 2 3 4

Nov 04 2008 11:45 PM 11-04-2008 04 Nov 08 04 Nov 2008 11:45:34:243

dimonchik.com

CAST ФУНКЦИЯ | Oracle PL/SQL •MySQL •SQL Server

Узнайте, как использовать Oracle / PLSQL функцию CAST с синтаксисом и примерами.

Описание

Функция Oracle / PLSQL CAST конвертирует (преобразует) один тип данных в другой.

Синтаксис

Синтаксис функции Oracle / PLSQL CAST:

CAST ( { expr | ( subquery ) | MULTISET ( subquery ) } AS type_name )

Примечание

Допускаются следующие преобразования:

Откуда Куда

char,varchar2	number	datetime /interval	raw	rowid,urowid	nchar,nvarchar2
char, varchar2	X	X	X	X	X
number	X	X
datetime / interval	X		X
Raw	X			X
rowid, urowid	X				X
nchar, nvarchar2		X	X	X	X	X

Применение

Функцию CAST можно использовать в следующих версиях Oracle / PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

Пример

Рассмотрим несколько примеров функции CAST и изучим, как использовать функцию CAST в Oracle / PLSQL.

SQL> select CAST( '22-Jul-2014' AS varchar2(30) ) AS RESULT FROM DUAL; RESULT ------------------------------ 22-Jul-2014

SQL> select CAST( '22-Jul-2014' AS varchar2(30) ) AS RESULT FROM DUAL;

RESULT

------------------------------

22-Jul-2014

Этот запрос конвертирует дату (то есть: 22-июл-2014) в varchar2 (30).

Рубрика: Функции преобразования Oracle / PLSQL | Permalink

oracleplsql.ru

Функции CAST и CONVERT (SQL Server Compact)

Эта документация перемещена в архив и не поддерживается.

Явно приводят выражение одного типа данных к другому. Функция CAST является синтаксическим вариантом функции CONVERT.

Syntax for CAST: CAST ( expression AS data_type [ (length ) ]) Syntax for CONVERT: CONVERT ( data_type [ ( length ) ] ,expression [ ,style ] ) data_type

Целевым типом данных, предоставляемым системой, является bigint. Пользовательские типы данных не применяются. Дополнительные сведения о доступных типах данных см. в разделе Типы данных.

Length

Необязательный параметр для типов данных nchar, nvarchar, char, varchar, binary и varbinary.

Expression

Любое допустимое в SQL Server Compact выражение. Дополнительные сведения см. в разделе Выражения (SQL Server Compact).

Style

Стиль формата даты, используемый для преобразования даты из типа datetime или smalldatetime в символьные данные (типы данных nchar, nvarchar, char, varchar, nchar или nvarchar), либо формат строки при преобразовании типов данных float, real, money или smallmoney в символьные данные (типы данных nchar, nvarchar, char, varchar, nchar или nvarchar). Если аргумент style имеет значение NULL, возвращаемое значение также будет равно NULL.

Дополнительные сведения см. в подразделе «Примечания» далее в этом разделе.

SQL Server Compact поддерживает формат даты в арабском стиле, при использовании кувейтского алгоритма.

В следующей таблице два столбца слева представляют значения аргумента style для преобразования данных типа datetime или smalldatetime в символьные данные. Чтобы получить четырехзначное значение года, включающее столетие (гггг), добавьте 100 к значению аргумента style.

Без столетия (гг)

Со столетием (гггг)

Стандартный

Ввод-вывод**

-	0 или 100 (*)	По умолчанию	мес дд гггг чч:ми AM (или PM)
1	101	США	мм/дд/гггг
2	102	ANSI	гг.мм.дд
3	103	Британский/французский	дд/мм/гг
4	104	Немецкий	дд.мм.гг
5	105	Итальянский	дд-мм-гг
6	106	-	дд мес гг
7	107	-	Мес дд, гг
8	108	-	чч:мм:сс
-	9 или 109 (*)	По умолчанию + миллисекунды	мес дд гггг чч:ми:сс:мсек AM (или PM)
10	110	США	мм-дд-гг
11	111	Японский	гг/мм/дд
12	112	ISO	Ггммдд
-	13 или 113 (*)	Европейский по умолчанию + миллисекунды	дд мес гггг чч:мм:сс:мсек (24-часовой формат)
14	114	-	чч:ми:сс:мсек (24-часовой формат)
-	20 или 120 (*)	Канонический формат ODBC	гггг-мм-дд чч:ми:сс (24-часовой формат)
-	21 или 121 (*)	Канонический формат ODBC (с миллисекундами)	гггг-мм-дд чч:ми:сс.мсек (24-часовой формат)
-	126(***)	ISO8601	гггг-мм-ддTчч:мм:сс.мсек (без пробелов)
-	130*	Хиджра****	дд мес гггг чч:мм:сс:мсек AM
-	131*	Хиджра****	дд/мм/гг чч:ми:сс:мсек AM

* Значения по умолчанию (style 0 или 100, 9 или 109, 13 или 113, 20 или 120 и 21 или 121) всегда возвращают столетие (гггг).

** Ввод при преобразовании в формат datetime. Вывод при преобразовании в символьные данные.

*** Для использования в XML. Для преобразования из типа данных datetime или smalldatetime в данные character формат вывода будет таким, как описано в таблице. При преобразовании из типа данных float, money или smallmoney в данные character формат вывода будет эквивалентен значению аргумента style 2. При преобразовании из типа данных real в данные character формат вывода будет эквивалентен значению аргумента style 1.

**** Хиджра — календарная система с несколькими вариантами, из которых в SQL Server Compact использует кувейтский алгоритм.

В следующей таблице показаны значения аргумента style для преобразования типов данных float и real в символьные данные.

Значение

Вывод

0 (по умолчанию)	Не более шести цифр. По необходимости используется экспоненциальное представление чисел.
1	Всегда 8 цифр. Всегда используется экспоненциальное представление чисел.
2	Всегда 16 цифр. Всегда используется экспоненциальное представление чисел.

В следующей таблице столбец слева представляет значения аргумента style для преобразования данных с типом money или smallmoney в символьные данные.

Значение

Вывод

0 (по умолчанию)	Без разделителей слева от десятичной запятой, две цифры справа от десятичной запятой. Пример: 4235.98.
1	С разделителями групп цифр слева от десятичной запятой, две цифры справа от десятичной запятой. Пример: 3,510.92.
2	Без разделителей групп слева от десятичной запятой, четыре цифры справа от десятичной запятой. Пример: 4235.9819.

Аргумент style 128 позволяет удалить конечные нули из результирующего набора при преобразовании данных с типом numeric или decimal в символьные данные.

Важно!

При использовании функций CAST и CONVERT с типом данных nchar, nvarchar, binary или varbinary, SQL Server усекает значения до 30 символов. SQL Server Compact допускает не более 4000 символов для типов данных nchar и nvarchar и не более 8000 символов для типов binary и varbinary. Вследствие этого результаты, сформированные запросами в SQL Server и SQL Server Compact, будут различаться. Если тип данных задан с указанием размера (например, nchar(200), nvarchar(200), binary(400), varbinary(400)), то и в SQL Server, и в SQL Server Compact результаты будут идентичны.

На следующем рисунке показано явное и неявное преобразование данных в SQL Server Compact.

В следующем примере значение с типом данных datetime преобразуется в значение nvarchar.

SELECT OrderDate, CONVERT(nvarchar(10), OrderDate, 101) FROM Orders

Функция CAST преобразует выражение одного типа данных в другой. В следующем примере результат преобразования равен 10.

SELECT CAST(10.6496 AS int)

Если при преобразовании целевой тип имеет меньшее число знаков после десятичной запятой, чем исходный, значение будет округлено. Например, результатом следующего преобразования будет $10,3497.

SELECT CAST(10.3496847 AS money)

technet.microsoft.com

Функции конвертации типов данных доступные в 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 позволяют гибко контролировать процесс конвертации строки в число или дату.

Exam tip

Понимание широко используемых масок форматирования проверяется достаточно просто вопросами вида: какой результат вернёт вызов функций 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” применяется к элементу даты чтобы сделать литературно правильным отображение даты как 17th или 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»

FROM 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 требует обязательного совпадения строки и маски форматирования. Если строка не совпадает с маской – возвращается ошибка. Рассмотрим несколько примеров

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 Hh34: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

Exam tip

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

oracledb.ru

Функция SQL CONVERT сервера ()

SQL Дата сервера Функции

Определение и использование

Функция CONVERT () является общая функция для преобразования даты в новый тип данных.

Функция CONVERT () может отображать данные о дате / времени в различных форматах.

грамматика

CONVERT(data_type(length),expression,style)

值描述

data_type(length)	规定目标数据类型（带有可选的长度）。
expression	规定需要转换的值。
style	规定日期/时间的输出格式。

В следующей таблице приведены значения стиля из DateTime или smalldatetime для символьных данных:

值 (世纪 yy) 值 (世纪 yyyy) 输入/输出标准

-	0 or 100	mon dd yyyy hh:miAM (or PM)	Default
1	101	mm/dd/yy	USA
2	102	yy.mm.dd	ANSI
3	103	dd/mm/yy	British/French
4	104	dd.mm.yy	German
5	105	dd-mm-yy	Italian
6	106	dd mon yy
7	107	Mon dd, yy
8	108	hh:mm:ss
-	9 or 109	mon dd yyyy hh:mi:ss:mmmAM (or PM)	Default+millisec
10	110	mm-dd-yy	USA
11	111	yy/mm/dd	Japan
12	112	yymmdd	ISO
-	13 or 113	dd mon yyyy hh:mi:ss:mmm (24h)
14	114	hh:mi:ss:mmm (24h)
-	20 or 120	yyyy-mm-dd hh:mi:ss (24h)
-	21 or 121	yyyy-mm-dd hh:mi:ss.mmm (24h)
-	126	yyyy-mm-ddThh:mi:ss.mmm (no spaces)	ISO8601
-	130	dd mon yyyy hh:mi:ss:mmmAM	Hijiri
-	131	dd/mm/yy hh:mi:ss:mmmAM	Hijiri

примеров

Следующий скрипт использует функцию CONVERT () для отображения другого формата. Мы будем использовать функцию GETDATE (), чтобы получить текущую дату / время:

CONVERT(VARCHAR(19),GETDATE()) CONVERT(VARCHAR(10),GETDATE(),10) CONVERT(VARCHAR(10),GETDATE(),110) CONVERT(VARCHAR(11),GETDATE(),6) CONVERT(VARCHAR(11),GETDATE(),106) CONVERT(VARCHAR(24),GETDATE(),113)

Результаты следующие:

Nov 04 2011 11:45 PM 11-04-11 11-04-2011 04 Nov 11 04 Nov 2011 04 Nov 2011 11:45:34:243

SQL Дата сервера Функции

www.w3big.com

TO_CHAR ФУНКЦИЯ | Oracle PL/SQL •MySQL •SQL Server

Узнайте, как использовать Oracle / PLSQL функцию TO_CHAR с синтаксисом и примерами.

Описание

Функция Oracle / PLSQL TO_CHAR преобразует число или дату в строку.

Синтаксис

Синтаксис функции Oracle / PLSQL TO_CHAR:

TO_CHAR( value, [ format_mask ], [ nls_language ] )

Параметры или аргументы

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

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

nls_language не является обязательным. Это язык NLS используется для преобразования значения в строку.

Применение

Функцию TO_CHAR можно использовать в следующих версиях Oracle / PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

Пример

Рассмотрим несколько примеров функции TO_CHAR и изучим, как использовать функцию TO_CHAR в Oracle / PLSQL.

Для чисел

Ниже приведены примеры для функции TO_CHAR для чисел.

SQL> SELECT TO_CHAR(1242.78, '9999.9') FROM DUAL; TO_CHAR(1242.78,'9999.9') ------------------------- 1242.8 SQL> SELECT TO_CHAR(-1242.78, '9999.9') FROM DUAL; TO_CHAR(-1242.78,'9999.9') -------------------------- -1242.8 SQL> SELECT TO_CHAR(1242.78, '9,999.99') FROM DUAL; TO_CHAR(1242.78,'9,999.99') --------------------------- 1,242.78 SQL> SELECT TO_CHAR(1242.78, '$9,999.00') FROM DUAL; TO_CHAR(1242.78,'$9,999.00') ---------------------------- $1,242.78 SQL> SELECT TO_CHAR(18, '000099') FROM DUAL; TO_CHAR(18,'000099') -------------------- 000018

SQL> SELECT TO_CHAR(1242.78, '9999.9') FROM DUAL;

TO_CHAR(1242.78,'9999.9')

-------------------------

1242.8

SQL> SELECT TO_CHAR(-1242.78, '9999.9') FROM DUAL;

TO_CHAR(-1242.78,'9999.9')

--------------------------

-1242.8

SQL> SELECT TO_CHAR(1242.78, '9,999.99') FROM DUAL;

TO_CHAR(1242.78,'9,999.99')

---------------------------

1,242.78

SQL> SELECT TO_CHAR(1242.78, '$9,999.00') FROM DUAL;

TO_CHAR(1242.78,'$9,999.00')

----------------------------

$1,242.78

SQL> SELECT TO_CHAR(18, '000099') FROM DUAL;

TO_CHAR(18,'000099')

--------------------

000018

Для дат

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

Параметр Пояснение

YEAR	Year, spelled out
YYYY	4-digit year
YYYYYY	Last 3, 2, or 1 digit(s) of year.
IYYIYI	Last 3, 2, or 1 digit(s) of ISO year.
IYYY	4-digit year based on the ISO standard
Q	Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM	Month (01-12; JAN = 01).
MON	Abbreviated name of month.
MONTH	Name of month, padded with blanks to length of 9 characters.
RM	Roman numeral month (I-XII; JAN = I).
WW	Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W	Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW	Week of year (1-52 or 1-53) based on the ISO standard.
D	Day of week (1-7).
DAY	Name of day.
DD	Day of month (1-31).
DDD	Day of year (1-366).
DY	Abbreviated name of day.
J	Julian day; the number of days since January 1, 4712 BC.
HH	Hour of day (1-12).
Hh22	Hour of day (1-12).
Hh34	Hour of day (0-23).
MI	Minute (0-59).
SS	Second (0-59).
SSSSS	Seconds past midnight (0-86399).
FF	Fractional seconds.

Ниже приведены примеры функции TO_CHAR для дат.

SQL> SELECT TO_CHAR(sysdate, 'yyyy/mm/dd') AS RESULT FROM DUAL; RESULT ---------- 2014/08/28 SQL> SELECT TO_CHAR(sysdate, 'yyyy.mm.dd') AS RESULT FROM DUAL; RESULT ---------- 2014.08.28 SQL> SELECT TO_CHAR(sysdate, 'Month DD, YYYY') AS RESULT FROM DUAL; RESULT ----------------- Август 28, 2014 SQL> SELECT TO_CHAR(sysdate, 'FMMonth DD, YYYY') AS RESULT FROM DUAL; RESULT ----------------- Август 28, 2014 SQL> SELECT TO_CHAR(sysdate, 'MON DDth, YYYY') AS RESULT FROM DUAL; RESULT -------------- АВГ 28TH, 2014 SQL> SELECT TO_CHAR(sysdate, 'FMMON DDth, YYYY') AS RESULT FROM DUAL; RESULT -------------- АВГ 28TH, 2014 SQL> SELECT TO_CHAR(sysdate, 'FMMon ddth, YYYY') AS RESULT FROM DUAL; RESULT -------------- Авг 28th, 2014

SQL> SELECT TO_CHAR(sysdate, 'yyyy/mm/dd') AS RESULT FROM DUAL;

RESULT

----------

2014/08/28

SQL> SELECT TO_CHAR(sysdate, 'yyyy.mm.dd') AS RESULT FROM DUAL;

RESULT

----------

2014.08.28

SQL> SELECT TO_CHAR(sysdate, 'Month DD, YYYY') AS RESULT FROM DUAL;

RESULT

-----------------

Август 28, 2014

SQL> SELECT TO_CHAR(sysdate, 'FMMonth DD, YYYY') AS RESULT FROM DUAL;

RESULT

-----------------

Август 28, 2014

SQL> SELECT TO_CHAR(sysdate, 'MON DDth, YYYY') AS RESULT FROM DUAL;

RESULT

--------------

АВГ 28TH, 2014

SQL> SELECT TO_CHAR(sysdate, 'FMMON DDth, YYYY') AS RESULT FROM DUAL;

RESULT

--------------

АВГ 28TH, 2014

SQL> SELECT TO_CHAR(sysdate, 'FMMon ddth, YYYY') AS RESULT FROM DUAL;

RESULT

--------------

Авг 28th, 2014

Вы заметите, что в некоторых примерах функции TO_CHAR, параметр format_mask начинается с «FM». Это означает, что нули и пробелы подавлены. Это можно увидеть в приведенных ниже примерах.

SQL> SELECT TO_CHAR(SYSDATE, 'FMMonth DD, YYYY') AS RESULT FROM DUAL; RESULT ----------------- Август 9, 2014 SQL> SELECT TO_CHAR(SYSDATE, 'FMMON DDth, YYYY') AS RESULT FROM DUAL; RESULT -------------- АВГ 9TH, 2014 SQL> SELECT TO_CHAR(SYSDATE, 'FMMon ddth, YYYY') AS RESULT FROM DUAL; RESULT -------------- Авг 9th, 2014

SQL> SELECT TO_CHAR(SYSDATE, 'FMMonth DD, YYYY') AS RESULT FROM DUAL;

RESULT

-----------------

Август 9, 2014

SQL> SELECT TO_CHAR(SYSDATE, 'FMMON DDth, YYYY') AS RESULT FROM DUAL;

RESULT

--------------

АВГ 9TH, 2014

SQL> SELECT TO_CHAR(SYSDATE, 'FMMon ddth, YYYY') AS RESULT FROM DUAL;

RESULT

--------------

Авг 9th, 2014

Нули были подавлены, так что день показывает, как «9», а не «09».

Вопросы и ответы

Вопрос:

Почему нельзя не отсортировать дни недели?

SELECT ename, hiredate, TO_CHAR((hiredate),’fmDay’) «Day»FROM empORDER BY «Day»;

Ответ:

В приведенном выше запросе SQL, маска формата fmDay использующаяся в функции TO_CHAR вернет имя дня, а не числовое значение дня.

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

SELECT ename, hiredate, TO_CHAR((hiredate),’fmD’) «Day»FROM empORDER BY «Day»;

oracleplsql.ru