Функция DATE_FORMAT
Функция DATE_FORMAT преобразует дату из формата год-месяц-день или формата год-месяц-день часы:минуты:секунды в другой удобный нам формат.
Синтаксис
SELECT DATE_FORMAT(дата, формат) FROM имя_таблицы WHERE условие
Команды
| Команда | Описание |
|---|---|
| %d | День месяца, число от 00 до 31 . |
| %e | День месяца, число от 0 до 31 . |
| %m | Месяц, число от 01 до 12 . |
| %c | Месяц, число от 1 до 12 . |
| %Y | Год, число, 4 цифры. |
| %y | Год, число, 2 цифры. |
| %j | День года, число от 001 до 366 . |
| %H | Час, число от 00 до 23 . |
| %k | Час, число от 0 до 23 . |
| %h | Час, число от 01 до 12 . |
| %I | Час, число от 01 до 12 . |
| %l | Час, число от 1 до 12 . |
| %i | Минуты, число от 00 до 59 . |
| %S | Секунды, число от 00 до 59 . |
| %s | Секунды, число от 00 до 59 . |
| %w | День недели (0 — воскресенье, 1 — понедельник). |
| %W | Название дня недели по-английски. |
| %a | Сокращенный день недели по-английски. |
| %M | Название месяца по-английски. |
| %b | Сокращенный месяц по-английски. |
| %D | День месяца с английским суффиксом (1st, 2nd, 3rd и т.д.). |
| %r | Время, 12 -часовой формат (hh:mm:ss [AP]M). |
| %T | Время, 24 -часовой формат (hh:mm:ss). |
| %p | AM или PM. |
| %U | Неделя, где воскресенье считается первым днем недели, число от 00 до 53 . |
| %u | Неделя, где понедельник считается первым днем недели, число от 00 до 53 . |
| %V | Неделя, где воскресенье считается первым днем недели, число от 01 до 53 . Используется с `%X’. |
| %v | Неделя, где понедельник считается первым днем недели, число от 01 до 53 . Используется с `%x’. |
| %X | Год для недели, где воскресенье считается первым днем недели, число, 4 цифры. Используется с ‘%V’ . |
| %x | Год для недели, где воскресенье считается первым днем недели, число, 4 разряда. Используется с ‘%v’ . |
| %% | Символ `%’. |
Таблицы для примеров
| id айди |
name имя |
date дата регистрации |
|---|---|---|
| 1 | user1 | 2010-03-01 12:01:02 |
| 2 | user2 | 2011-04-02 13:02:03 |
| 3 | user3 | 2012-05-03 14:03:04 |
Пример
В данном примере при выборке создается новое поле, в котором будет лежать дата в другом формате:
SELECT *, DATE_FORMAT(date, ‘%d.%m.%Y’) as new_date FROM employees
Результат выполнения кода:
| id айди |
name имя |
date дата регистрации |
new_date дата в новом формате |
|---|---|---|---|
| 1 | user1 | 2010-03-01 12:01:02 | 01.03.2010 |
| 2 | user2 | 2011-04-02 13:02:03 | 02.04.2011 |
| 3 | user3 | 2012-05-03 14:03:04 | 03.05.2012 |
Смотрите также
- функцию TIME_FORMAT ,
которая меняет формат вывода времени
Как неявно конвертируется тип данных DATE в NUMBER?
Оно вернёт значение: 2459060 Никак не пойму, откуда берётся это число? Так как, SYSDATE-SYSDATE это число, но дату же нельзя так просто преобразовать в число:
TO_NUMBER(DATE'2020-07-29') ORA-01722: invalid number В документации SQL Language Reference NVL2 сказано, что если выражение второго аргумента число, то последующий аргумент будет неявно преобразован к числу:
If expr2 is numeric data, then Oracle Database determines which argument has the highest numeric precedence, implicitly converts the other argument to that data type, and returns that data type.
Поэтому вопрос, какая конвертация используется неявно, чтобы тип данных DATE преобразовать в NUMBER ? Свободный перевод вопроса How does Oracle SQL convert DATE to NUMBER implicitly? от участника @Nicholas Koldys
Отслеживать
задан 29 июл 2020 в 19:56
51.6k 201 201 золотой знак 65 65 серебряных знаков 246 246 бронзовых знаков
ассоциация:stackoverflow.com/q/61992011
31 июл 2020 в 13:15
2 ответа 2
Сортировка: Сброс на вариант по умолчанию
По факту выполняется это:
select to_number (to_char (date'2020-07-29','J')) days from dual; DAYS ---------- 2459060 Где J , это число дней прошедших с 1 января 4712 до н.э., то есть то значение, которое возвращает выражение в вопросе.
Не совсем очевидно, что это должно быть так, но это так. Если второй аргумент задать явно числом, то получим ошибку:
select nvl2 (null, 99, date'2020-05-29') from dual; Error report - SQL Error: ORA-00932: inconsistent datatypes: expected NUMBER got DATE Если сдедлать дамп результата вычитания дат, то он возвращает не число (Typ=2), а другой, внутренний и недокументированный, насколько я вижу, тип данных:
select dump (sysdate-sysdate) dump from dual; DUMP -------------------------------- Typ=14 Len=8: 0,0,0,0,0,0,0,0 Очевидно, что это приводит к тому, что третий аргумент приводится к тому же самому типу, к которому он наиболее эквивалентен:
select date'2020-07-29'-date'-4712-01-01' days from dual; DAYS ---------- 2459059 Поэтому, выглядит так, что либо делается что-то похожее на последнее, но адаптироанное под этот тип данных, либо делается внутреннее преобразование к формату J , либо что-то ещё другое. Повидимому, это поведение не документированно.
Свободный перевод ответа от участника @Alex Poole
Отслеживать
ответ дан 29 июл 2020 в 19:56
51.6k 201 201 золотой знак 65 65 серебряных знаков 246 246 бронзовых знаков
Здесь немного подробнее о дате 1 января 4712 до н.э. — «Oracle database internally stores date in Julian calendar format, Julian dates (abbreviated JD) are simply a continuous count of days and fractions since noon Universal Time on January 1, 4713 BCE (on the Julian calendar). For example, today i.e, 23-AUG-2015 is the 2457258th day from 01-JAN-4713 BC of Julian calendar.»
29 июл 2020 в 20:04
Спасибо @MaxU! Это скорее всего будет тема моего авторского ответа ;))
29 июл 2020 в 20:10
Тип результата выражения (date2-date1) не является числом. К численному типу NUMBER он будет приведён при присвоении значения переменной или колонке таблицы. До того, это внутренняя (недокументированная) си структура данных с типом ( Typ=14 ):
var rc refcursor declare dd number := sysdate-(sysdate-125.125); begin open :rc for select dump (sysdate-(sysdate-125.125), 16) dump, dump (dd, 16) dump, dd ddiff from dual; end; / DUMP DUMP DDIFF -------------------------------- -------------------------------- ---------- Typ=14 Len=8: 7d,0,0,0,30,2a,0,0 Typ=2 Len=5: c2,2,1a,d,33 125.125 Где, длина 8 байт, 4 байта — кол-во дней, 3 байта кол-во секунд, последний байт резервирован. На архитектуре с little endian можно посчитать справa налево:
select to_number ('7d', 'xx') days, to_number ('2a30', 'xxxx')/(60*60) hours from dual / DAYS HOURS ---------- ---------- 125 3 То есть, по сути это интервал времени. Выглядит логично, привести дату к интервалу времени используя Юлианскую дату (Julian Days) как вычитаемое:
select (date'2020-07-29')-date'-4712-01-01'+1 days from dual; DAYS ---------- 2459060 Кроме того, результат вычитания дат, т.е. эту внутреннюю структуру данных, можно привести к стандартному типу данных INTERVAL , что с подстановкой просто численного литерала даст ошибку:
select (sysdate-(sysdate-125.125)) day (3) to second interval from dual; INTERVAL -------------------- +125 03:00:00.000000 select (125.125) day (3) to second interval from dual; Error report - SQL Error: ORA-30083: syntax error was found in interval value expression SQL. Как привести дату в строку в нужный формат в SQL Server
На выходе получается дату в строковом виде в нужном формате.
Что еще посмотреть по SQL Server
Выгода от использования Falcon Space
В 2-3 раза экономнее и быстрее, чем заказная разработка
Более гибкая, чем коробочные решения и облачные сервисы
Используйте готовые решения и изменяйте под свои потребности
Нужна бесплатная консультация?
Получить оценку проекта
Создайте концепцию проекта на основе нашего шаблона и получите оценку проекта в виде КП.
Демо-сайт решений
Базисные решения, которые можно гибко адаптировать под себя: менять внешний вид, бизнес-логику и даже структуру базы данных.
MySQL: Преобразование даты с помощью DATE_FORMAT()
Даты хранятся в mysql в обратном порядке год-месяц-число(2008-10-18), иногда даже без разделителя(20081018). Чтобы вывести дату, нужно ее преобразовать в нормальный читаемый вид.
Тут есть два способа преобразования, эффективный и не очень. Не эффктивный способ это когда дату выводимую из mysql преобразуют с помощью php.
Сам лично так делал очень долгое время. Перед выводом на экран переворачивал каждую дату с помощью php функции.
Если количество преобразований не большое, то можно переворачивать дату и с помощью php, ничего плохого тут нет, но если нужно будет выдернуть десятки-сотни тысяч записей и в каждой преобразовать дату, то тут конечно намного быстрее будет преобразование дат с помощью mysql.
В mysql существует отличная функция DATE_FORMAT(), она очень похожа на php функцию date().
Вот пример использования SELECT DATE_FORMAT(«2014-11-19»,’%d.%m.%Y’); результат 19.11.2014
Все очень просто и быстро, нет необходимости изменять формат даты с помощью php.
Вот список определителей этой функции:
| Определитель | Описание |
| %M | Название месяца (январь. декабрь) |
| %W | Название дня недели (воскресенье. суббота) |
| %D | День месяца с английским суффиксом (0st, 1st, 2nd, 3rd и т.д.) |
| %Y | Год, число, 4 разряда |
| %y | Год, число, 2 разряда |
| %X | Год для недели, где воскресенье считается первым днем недели, число, 4 разряда, используется с ‘%V’ |
| %x | Год для недели, где воскресенье считается первым днем недели, число, 4 разряда, используется с ‘%v’ |
| %a | Сокращенное наименование дня недели (Вс. Сб) |
| %d | День месяца, число (00..31) |
| %e | День месяца, число (0..31) |
| %m | Месяц, число (00..12) |
| %c | Месяц, число (0..12) |
| %b | Сокращенное наименование месяца (Янв. Дек) |
| %j | День года (001..366) |
| %H | Час (00..23) |
| %k | Час (0..23) |
| %h | Час (01..12) |
| %I | Час (01..12) |
| %l | Час (1..12) |
| %i | Минуты, число (00..59) |
| %r | Время, 12-часовой формат (hh:mm:ss [AP]M) |
| %T | Время, 24-часовой формат (hh:mm:ss) |
| %S | Секунды (00..59) |
| %s | Секунды (00..59) |
| %p | AM или PM |
| %w | День недели (0=воскресенье..6=суббота) |
| %U | Неделя (00..53), где воскресенье считается первым днем недели |
| %u | Неделя (00..53), где понедельник считается первым днем недели |
| %V | Неделя (01..53), где воскресенье считается первым днем недели. Используется с `%X’ |
| %v | Неделя (01..53), где понедельник считается первым днем недели. Используется с `%x’ |
| %% | Литерал `%’. |