FORMAT: примеры (SQL)

Форматирование данных с помощью функции FORMAT в SQL Server
Раздел: Даты и времени функции, Дата и время
FORMAT(value, format [, culture]): nvarchar
типовые примерыFORMAT на mySQL, Oracle, PostgreSQL, SQLiteFORMAT изменения sqlпримеры расширенные

Функция FORMAT в MS SQL Server

Функция FORMAT выполняет форматирование числовых, датированных и строковых значений в соответствии с указанным шаблоном и необязательным указанием языка (культуры). Она применяется для преобразования данных в удобочитаемый вид непосредственно в SQL-запросах, особенно при формировании отчетов.

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

FORMAT( value, format [, culture ] )

Аргументы:

  • value — выражение любого типа данных, поддерживающего преобразование в строку. Это может быть тип данных даты/времени, числовой тип или строковый.
  • format — строка формата nvarchar, определяющая шаблон вывода. Шаблон может быть стандартным или пользовательским, аналогичным используемому в .NET Framework.
  • culture — необязательный строковый аргумент nvarchar, задающий язык и региональные параметры. Если аргумент не указан, используется язык текущего сеанса. Примеры: 'ru-RU', 'en-US'.

Возвращаемое значение: функция возвращает значение типа nvarchar или NULL, если аргумент value равен NULL. Длина возвращаемой строки определяется шаблоном и форматом.

Примеры использования функции FORMAT

Форматирование даты:

SELECT FORMAT(GETDATE(), 'dd.MM.yyyy', 'ru-RU')
28.12.2023

Форматирование числа с разделителями тысяч:

SELECT FORMAT(1234567.89, 'N', 'ru-RU')
1 234 567,89

Форматирование валюты:

SELECT FORMAT(1500.75, 'C', 'en-US')
$1,500.75

Процентный формат:

SELECT FORMAT(0.456, 'P', 'ru-RU')
45,60 %

Пользовательский формат даты:

SELECT FORMAT(SYSDATETIME(), 'dddd, dd MMMM yyyy', 'ru-RU')
четверг, 28 декабря 2023

Альтернативные функции форматирования в MS SQL

В MS SQL существуют функции для явного преобразования типов и форматирования:

  • CONVERT и CAST — преобразуют данные в другой тип, но не предлагают гибкого форматирования. CONVERT для дат имеет стили, но их набор ограничен. Предпочтительны для преобразования типов, а не для красивого вывода.
  • STR — преобразует числовые данные в строку с указанием длины и десятичных разрядов. Подходит для простого форматирования чисел, но менее гибкая, чем FORMAT.
  • Функции работы с датами (DATENAME, DATEPART) — позволяют извлекать части даты и комбинировать их в строку. Используются, когда нужна максимальная производительность и FORMAT не подходит из-за затратности.

Функцию FORMAT удобно использовать, когда важна читаемость и гибкость форматирования, а производительность не критична.

Типичные ошибки при использовании

Использование неподдерживаемого формата для типа данных: применение шаблона даты к числу вызывает ошибку.

SELECT FORMAT(123, 'dd.MM.yyyy')
Ошибка: Аргумент данных date неявно преобразован в тип данных int.

Некорректное указание культуры: указание несуществующей культуры.

SELECT FORMAT(GETDATE(), 'D', 'xx-XX')
Ошибка: Недопустимый параметр culture 'xx-XX'.

Потеря производительности: использование функции в условиях WHERE или на больших наборах данных замедляет выполнение запроса, так как FORMAT является ресурсоемкой.

Изменения в последних версиях MS SQL

Функция FORMAT была введена в SQL Server 2012. Значительных изменений в ее работе не было. Однако, с обновлениями .NET Framework расширяется поддержка культур и форматов. В версии SQL Server 2016 добавлена поддержка форматов для типа данных time.

Расширенные примеры применения FORMAT

Форматирование номера телефона:

Пример sql
SELECT FORMAT(79991234567, '+# (###) ###-####')
+7 (999) 123-4567

Форматирование времени с миллисекундами:

Пример sql
SELECT FORMAT(SYSDATETIME(), 'HH:mm:ss.fff')
14:35:22.123

Форматирование числового диапазона:

Пример sql
SELECT FORMAT(5, '00') + ' - ' + FORMAT(10, '00')
05 - 10

Использование в выражении CASE для динамического форматирования:

Пример sql
SELECT
  ProductID,
  CASE
    WHEN Price > 1000 THEN FORMAT(Price, 'C', 'en-US')
    ELSE FORMAT(Price, 'N2', 'ru-RU')
  END AS FormattedPrice
FROM Products
1 $1,500.00
2 345,67

Форматирование как порядкового номера:

Пример sql
SELECT FORMAT(22, '##-е')
22-е

Аналоги функции в других СУБД и языках

MySQL: функция DATE_FORMAT для дат и FORMAT для чисел.

SELECT DATE_FORMAT(NOW(), '%d.%m.%Y');
SELECT FORMAT(1234567.89, 2, 'ru_RU');
28.12.2023
1 234 567,89

Oracle: функция TO_CHAR для форматирования дат и чисел.

SELECT TO_CHAR(SYSDATE, 'DD.MM.YYYY') FROM dual;
SELECT TO_CHAR(1234567.89, '999G999G999D00', 'NLS_NUMERIC_CHARACTERS='', ''') FROM dual;
28.12.2023
1 234 567,89

PostgreSQL: функция TO_CHAR, аналогичная Oracle.

SELECT TO_CHAR(CURRENT_DATE, 'DD.MM.YYYY');
SELECT TO_CHAR(1234567.89, '999G999G999D00');
28.12.2023
1 234 567,89

SQLite: встроенной функции нет, форматирование выполняется с помощью функций strftime для дат и конкатенации для чисел.

MS SQL FORMAT function comments

En
FORMAT Returns a value formatted with the specified format