Sql преобразовать формат даты

Sql преобразовать формат даты

Как известно, все даты хранятся в mysql в обратном порядке год-месяц-число(2008-10-18), иногда даже без разделителя(20081018).
Чтобы вывести дату, нужно ее преобразовать в нормальный читаемый вид.

Тут есть два способа преобразования, эффективный и не очень.
Не эффктивный способ это когда дату выводимую из mysql преобразуют с помощью php.
Сам лично так делал очень долгое время. Перед выводом на экран переворачивал каждую дату с помощью php функции.
Если количество преобразований не большое, то можно переворачивать дату и с помощью php, ничего плохого тут нет, но если нужно будет выдернуть десятки-сотни тысяч записей и в каждой преобразовать дату, то тут конечно намного быстрее будет преобразование дат с помощью mysql.

В mysql существует отличная функция DATE_FORMAT(), она очень похожа на php функцию date().
Вот пример использования

SELECT DATE_FORMAT("2008-11-19",’%d.%m.%Y’);
результат

Все очень просто и быстро, нет необходимости крутить даты с помощью 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’
%% Литерал `%’.
Читайте также:  Gosuslugi top что это

Комментарии

27.11.2008 —-
Ай шайтан.
Сам занимаюсь лет пять php и mysql, и все время переделывал дату в php.
Даже в голову не приходило, что проще использовать втроенную функцию mysql

28.11.2008 Жека
Аналогично! Всегда пользовался своей функцией на php

03.12.2008 Сергей
Ну а вообще, кто то хоть пользуется таким подходом?
Или все используют php для переворота даты?
Я лично никогда не переворачивал дату в mysql, до сих пор все в php делаю

28.06.2009 Илья
К сожалению, ничего не получилось 🙁

08.07.2009 Виталий
Круто, спасибо за функцию. Интересно, а ёщё какие нибуть загогулины есть?

14.07.2009 DSaint
Спасибо, очень помогло. Осталось только название месяца на русском отобразить)

28.07.2009 Влад
mysql=>PHP
select unix_timestamp(start_date) as start_date_php
php-code
date(‘d.m.Y’,$row[‘start_date_php’])

PHP=>MySQL
update table set start_date=DATE_FORMAT(STR_TO_DATE(‘19.12.2009 18:35:22′,’%d.%m.%Y %H:%i’),’%Y.%m.%d %H:%i’)

18.08.2009 Гость
2: DSaint
Есть такая чудная функция:
ELT( MONTH(‘2004-04-10’), ‘Янв.’,’Фев.’,’Март’,’Апр.’,’Май’,’Июнь’,’Июль’,’Авг.’,’Сен.’,’Окт.’,’Ноя.’,’Дек.’)

29.10.2009 Владимир
Спасибо, про ELT( MONTH(‘2004-04-10’), ‘Янв.’,’Фев.’,’Март’,’Апр.’,’Май’,’Июнь’,’Июль’,’Авг.’,’Сен.’,’Окт.’,’Ноя.’,’Дек.’)
я не слыхал.

07.10.2010 Евгений
А что быстрее работает? Преобразование в запросе, или в результате работы php-функции?

07.10.2010 defender
Как минимум меньше памяти на обработку меньше вызовов функций меньше выделений памяти. Постоянно подобным пользуюсь только не в mysql а в postgresql.

08.10.2010 Админ
Евгений, вот defender правильно сказал, через базу это преобразование должно сработать красивее, но конечно если речь идет об извлечении огромного количества данных.
Если вы дергаете 10-20 записей, то нет никакой разницы как преобразовать дату, по сравнению с остальными нагрузками это мелочь.

27.01.2011 pcemma
ухх спасибо аффтору (: отпала нужда юзать свою мега крутую функцию для преобразования (:

13.04.2011 Xes
кАК ЕЕ ПОЛЬЗОВАТЬ В ПХП
while ($sqlr=mysql_fetch_array($sql))
<
echo ($sqlr[‘comadd’].’ ‘.$sqlr[‘comment’].’

$sqlr[‘comadd’] — Надо в нормальной форме представитт?
>

14.04.2011 Виталий
У меня в базе дата записана в формате 19.11.2008 тип таблицы VARCHAR, как ее перезаписать в базу в формате 2008-11-19?
Руками просто очень долго.
Спасибо.

15.04.2011 админ
Xes, это функция MySQL, использовать ее нужно в запросе sql, который судя по вашему коду находится где-то выше. В данном участке кода ее нельзя использовать.

Читайте также:  Айфоны мерзли на морозе

15.04.2011 админ
Виталий, просто измените тип ячейки на DATE, mysql автоматически переведет все данные в этой ячейке именно в формат 2008-11-19.
Но на всякий случай перед изменением типа ячейки сделайте дамп этой таблицы, так как вдруг база что то не то сделает и таблица сломается вообще.

Если вам важно оставить тип поля varchar, то после того, как вы установите тип DATE, установите обратно varchar.

Это самый простой вариант, но не совсем правильный, но я проверил, он работает.

24.05.2011 Константин
а я всегда беру SELECT *,UNIX_TIMESTAMP(created) AS created FROM.
а мотом могу хоть в каком формате. Хоть только день, хоть только время.
и сравнивать, что больше 14.05.2011 или 14.05.2010.
А записываю так:
. date=’.gmdate("Y-m-d H:i:s",$created).
и вобщем-то, не вижу причин менять привычки

24.05.2011 сергей
Константин, да я и сам использую для вывода php date(), просто тут рассмотрен вариант преобразования даты не через php, а через mysql.

Я бы сказал, что это просто обзор mysql функции и она конечно имеет право на существование.

В таблицах БД нашей СРМ-ки дата хранится в очень странном формате: YYYY-DD-MM (и, да, это еще строка). Поступило задание залить в таблицу данные из другой БД, где дата хранится в подобающем ей формате DATETIME2(YYYY-MM-DD) .

Как конвертнуть дату в строку с изменением формата?

2 ответа 2

Самый простой способ это воспользоваться функцией CONVERT

Это выглядит вот так:

Вот список допустимых шаблонов форматов даты:

И результат выполнения

Будьте аккуратны с форматом YYYY-MM-DD, он зависит от настроек языка. Лучший стандарт по ISO — YYYYMMDD. Более подробно можно прочитать тут, но на английском.

Что делать, если нет нужного формата

Можно воспользоваться следующим методом:

Как вы понимаете на больших объёмах это будет работать не очень быстро.

Начиная с SQL Server 2012 появилась встроенная функция FORMAT:

Больше вариантов по произвольному формированию даты можно посмотреть тут

понедельник, 2 мая 2011 г.

Формат даты

Для вывода даты в формате DD.MM.YYYY

SomeDate = CONVERT(VarChar(50), getdate(), 104)

Выведется текущая дата в указанном формате. Формат указывается цифрами, в данном случае 104.

Читайте также:  At89c2051 24pu описание на русском

SELECT CONVERT(varchar, getdate(), 2) — ‘yy.mm.dd’
SELECT CONVERT(varchar, getdate(), 3) — ‘dd/mm/yy’
SELECT CONVERT(varchar, getdate(), 4) — ‘dd.mm.yy’
SELECT CONVERT(varchar, getdate(), 5) — ‘dd-mm-yy’
SELECT CONVERT(varchar, getdate(), 6) — ‘dd mmm yy’
SELECT CONVERT(VarChar, GETDATE(), 101) — ‘mm/dd/yyyy’
SELECT CONVERT(VarChar, GETDATE(), 102) — ‘yyyy.mm.dd’
SELECT CONVERT(VarChar, GETDATE(), 103) — ‘dd/mm/yyyy’
SELECT CONVERT(VarChar, GETDATE(), 104) — ‘dd.mm.yyyy’
SELECT CONVERT(varchar, GETDATE(), 105) — ‘dd-mm-yyy’
SELECT CONVERT(varchar, GETDATE(), 111) — ‘yyyy/mm/dd’
SELECT CONVERT(varchar, GETDATE(), 112) — ‘yyyymmdd’

SELECT CONVERT(varchar,GETDATE(),108) ‘hh:mm:ss’
SELECT CONVERT(varchar,GETDATE(),114) ‘hh:mm:ss:mmm’
SELECT CONVERT(varchar,GETDATE(),113) ‘dd Mmm yyyy hh:mm:ss:mmm’
SELECT CONVERT(VarChar(50), GETDATE(), 100) ‘mmm dd yyyy mm:ss AM’

и еще:

SELECT CONVERT(varchar,GETDATE(),0)
Return Value = Jun 6 2007 11:07PM
SELECT CONVERT(varchar,GETDATE(),100)
Return Value = Jun 6 2007 11:07PM

SELECT CONVERT(varchar,GETDATE(),1)
Return Value = 06/06/07
SELECT CONVERT(varchar,GETDATE(),101)
Return Value = 06/06/2007

SELECT CONVERT(varchar,GETDATE(),2)
Return Value = 07.06.06
SELECT CONVERT(varchar,GETDATE(),102)
Return Value = 2007.06.06

SELECT CONVERT(varchar,GETDATE(),3)
Return Value = 06/06/07
SELECT CONVERT(varchar,GETDATE(),103)
Return Value = 06/06/2007

SELECT CONVERT(varchar,GETDATE(),4)
Return Value = 06.06.07
SELECT CONVERT(varchar,GETDATE(),104)
Return Value = 06.06.2007

SELECT CONVERT(varchar,GETDATE(),5)
Return Value = 06-06-07
SELECT CONVERT(varchar,GETDATE(),105)
Return Value = 06-06-2007

SELECT CONVERT(varchar,GETDATE(),6)
Return Value = 06 Jun 07
SELECT CONVERT(varchar,GETDATE(),106)
Return Value = 06 Jun 2007

SELECT CONVERT(varchar,GETDATE(),7)
Return Value = Jun 06, 07
SELECT CONVERT(varchar,GETDATE(),107)
Return Value = Jun 06, 2007

SELECT CONVERT(varchar,GETDATE(),8)
Return Value = 23:38:49
SELECT CONVERT(varchar,GETDATE(),108)
Return Value = 23:38:49

SELECT CONVERT(varchar,GETDATE(),9)
Return Value = Jun 6 2007 11:39:17:060PM
SELECT CONVERT(varchar,GETDATE(),109)
Return Value = Jun 6 2007 11:39:17:060PM

SELECT CONVERT(varchar,GETDATE(),10)
Return Value = 06-06-07
SELECT CONVERT(varchar,GETDATE(),110)
Return Value = 06-06-2007

SELECT CONVERT(varchar,GETDATE(),11)
Return Value = 07/06/06
SELECT CONVERT(varchar,GETDATE(),111)
Return Value = 2007/06/06

SELECT CONVERT(varchar,GETDATE(),12)
Return Value = 070606
SELECT CONVERT(varchar,GETDATE(),112)
Return Value = 20070606

SELECT CONVERT(varchar,GETDATE(),13)
Return Value = 06 Jun 2007 23:40:14:577
SELECT CONVERT(varchar,GETDATE(),113)
Return Value = 06 Jun 2007 23:40:14:577

SELECT CONVERT(varchar,GETDATE(),14)
Return Value = 23:40:29:717
SELECT CONVERT(varchar,GETDATE(),114)
Return Value = 23:40:29:717

SELECT CONVERT(varchar,GETDATE(),20)
Return Value = 2007-06-06 23:40:51
SELECT CONVERT(varchar,GETDATE(),120)
Return Value = 2007-06-06 23:40:51

SELECT CONVERT(varchar,GETDATE(),21)
Return Value = 2007-06-06 23:41:10.153
SELECT CONVERT(varchar,GETDATE(),121)
Return Value = 2007-06-06 23:41:10.153

SELECT CONVERT(varchar,GETDATE(),126)
Return Value = 2007-06-06T23:41:10.153

SELECT CONVERT(varchar,GETDATE(),131)
Return Value = 21/05/1428 11:41:10:153PM

Ссылка на основную публикацию
Adblock detector