Как указать формат даты в sql

Time and Date Format in SQL

An SQL database stores a variety of data types, such as numbers, text strings, boolean values, dates, etc. However, storing and handling such data have their own specifications. The most difficult part when working with dates is to be sure that the format of the date you are trying to insert, matches the date format of the date column in the database.

SQL Date and Time Data Types

MySQL comes with the following data types for storing a date or a date/time value in the database:

DATE — YYYY-MM-DD
DATETIME — YYYY-MM-DD HH:MI
TIMESTAMP — YYYY-MM-DD HH:MI
YEAR — YYYY or YY

Convert DateTime to Date Format YYYY-MM-DD

While working with database tables, we need only the date part from the DateTime column. Let us first create an Employee Table in MySQL.

Output

Now, as we can see, the DOB column has DateTime format, we can convert this DateTime to YYYY-MM-DD which is a date datatype in SQL using DATE_FORMAT .

DATE_FORMAT(date, format) — Formats the date value according to the format string. The % character is required before format specifier characters.

Now, let’s query both the original DOB column values and DOB_new values to demonstrate the difference between them:

Output

Convert String to Date Format YYYY-MM-DD

STR_TO_DATE(str,format) — This is the inverse of the DATE_FORMAT() function. This STR_TO_DATE() function takes a string str and a format string format. STR_TO_DATE() returns a DATETIME value if the format string contains both date and time parts, or a DATE or TIME value if the string contains only date or time parts. If the date, time, or datetime value extracted from str is illegal, STR_TO_DATE() returns NULL and produces a warning.

Output

The server scans the str parameter in an attempt to match the format. The format string can contain literal characters and format specifiers beginning with % . The Literal character string is a sequence of zero or more characters enclosed by single quotes, such that the format must match literally in str . Format specifiers in the format must match a date or time part in str .

Convert Date Format YYYY-MM-DD into Other Formats

Similar to how we can convert other DateTime formats to YYYY-MM-DD , we can do the reverse. To convert the YYYY-MM-DD date format to different date formats, you can use the CONVERT function in MySQL.

The CONVERT() function converts a value into the specified datatype or character set.

Syntax:

Parameter Values:

• value: the required value to convert
• type: the required datatype to convert to which can be DATE, TIME, DATETIME, etc.

Output

In the above snippet, the first query converts the date format in SQL from YYYY-MM-DD to DATETIME format which gives YYYY-MM-DD HH:MI:SS as the output. While the second SQL query converts the YYYY-MM-DD HH:MI:SS to TIME format and gives HH:MI:SS as the output.

Date and Time Functions

In the following section, we will learn about some of the most important built-in date functions in SQL.

1. ADDDATE()

The ADDDATE() function adds a time/date interval to a date and then returns the date datatype in SQL.

Syntax

Parameter Values —

• date: the date to be modified
• days: the number of days to add to date
• value: the value of the time/date interval to add. Both positive and negative values are allowed.
• addunit: the type of interval to add.

Output

The last query in the above snippet is a bit different than the other two as ADDDATE(expression, days) is a shorthand way of modifying the date by just specifying the number of days as a parameter in the ADDDATE() function, this special form differentiates ADDDATE() from DATE_ADD() . In fact, when using this syntax, the ADDDATE() function is a synonym for DATE_ADD() .

2. ADDTIME()

The ADDTIME() function in SQL adds a time interval to a time/datetime and then returns the time/datetime.

Syntax

Parameter Values

• datetime: the time/datetime to be modified
• addtime: the time interval to add to datetime. Both positive and negative values are allowed

3. DATE_ADD()

The DATE_ADD() function adds a time/date interval to a date and then returns the date datatype in SQL.

Syntax

Parameter Values

• date: the date to be modified
• value: the value of the time/date interval to add. Both positive and negative values are allowed.
• addunit: the type of interval to add.

Output

The first two queries are exactly the same as the ADDDATE() syntax. In fact, when using this syntax, the ADDDATE() function in SQL is a synonym for DATE_ADD() . The last query in the above snippet shows an error because DATE_ADD() function doesn’t support the shorthand syntax.

4. FROM_DAYS()

The FROM_DAYS() function returns a date datatype in SQL from a numeric datevalue. The FROM_DAYS() function is to be used only with dates within the Gregorian calendar.

The Gregorian calendar was introduced in October 1582 as a modification of, and replacement for, the Julian calendar. The Julian calendar day Thursday, 4 October 1582 was followed by the first day of the Gregorian calendar, Friday, 15 October 1582 (the cycle of weekdays was not affected).

The main difference between Julian and Gregorian calendars is that an average year in Julian calendar is 365.25 days while an average year in Gregorian calendar is 365.2425 days.

Syntax

Parameter Values

• number the numeric day to convert to a date

Output

5. SEC_TO_TIME()

The SEC_TO_TIME() function returns a time value (in format HH:MM:SS ) based on the specified seconds.

Syntax

Parameter Values

• seconds the number of seconds. Both positive and negative values are allowed

Output

6. CURRENT_TIMEZONE()

This function returns the name of the time zone observed by a server. For SQL, the return value is based on the time zone of the instance itself assigned during instance creation, not the time zone of the underlying operating system.

Syntax

Argument This function takes no arguments.

Return Value CURRENT_TIMEZONE is a non-deterministic function. Views and expressions that reference this column cannot be indexed.

Некоторые примеры нестандартных возможностей синтаксиса sql. Часть вторая: форматы дат

Данная статья посвящена форматам дат в Oracle и некоторым особенностям их обработки. В статье приведен обзор нескольких стандартных масок форматирования дат, явная и неявная конвертация строк в даты и дополнительные параметры, влияющие на этот процесс. Как и в первой части статьи, обсуждение материала происходит на основе примеров, демонстрирующих нестандартные возможности форматирования. Детально рассмотрены механизмы Oracle, участвующие в процессе неявного преобразования. Описание большинства возможностей сопровождается ссылками на соответствующие разделы документации.

Изначально я не планировал написание статьи о датах, а собирался остановиться на рассмотрении всего одного вопроса данной тематики. Однако в ходе работы возникла необходимость в освещении различных дополнительных возможностей Oracle, стали появляться новые примеры. Так рассмотрение одного вопроса разрослось в небольшую статью. Надеюсь, получилось нескучно, несмотря на не самую интересную тематику.

Первая часть статьи, посвященная особенностям оператора order by, функционированию not in и примеру неявного преобразования типов, находится здесь.

Функция to_date и форматы даты

Мало кто из программистов любит тематику форматирования. Например, на некоторых курсах темы форматирования дат и региональных стандартов специально ставят на последние часы последнего дня тренинга, т.к. слушателям нудно. Причина в большом количестве существующих форматов при относительно редком их использовании в стандартных задачах. Чаще всего маски используются в трех функциях: to_number, to_char и to_date. Во всех трех случаях маска идет вторым необязательным параметром. И если масок для форматирования чисел еще более-менее вменяемое количество, то масок для форматирования дат до неприличия много, плюс еще суффиксы и модификаторы.

Безусловно, доступность большого количества масок является позитивным моментом, поскольку расширяет возможности, например, проверить является ли 13 сентября 2011 днем программиста, можно с помощью маски ‘DDD’, которая возвращает номер дня в году:

Несмотря на явную пользу форматирования, я не планировал включать во вторую часть статьи обзор форматов дат и примеры использования экзотических масок. Во-первых, вряд ли это будет кому-то интересно, во-вторых, автор также не является большим почитателем сложного форматирования, поскольку редко его применяет в жизни. Единственная причина появления данного раздела – некоторые вопросы, возникшие у читателей по поводу использования формата RR.

Перед тем как перейти непосредственно к основной теме раздела, давайте рассмотрим несколько нестандартных примеров работы с датами.

Пример №1. Использование урезанных шаблонов
Начнем со стандартного форматирования. Пускай сегодняшняя дата 16.09.2011, выполнятся ли следующие запросы, и что они вернут?

Запрос №2 является типичным примером конвертации даты в строку с приведением ее к нужному формату. Единственное отличие – вместо более привычных масок вида ‘DD.MM.YY’ или ‘DD-MON-YYYY’ мы использовали маску, задающую только год. Запрос №2 выполнится успешно и вернет текущий год в четырехзначном формате, т.е. ‘2011’.

Запрос №3 немного интереснее, он представляет собой типичный пример явного преобразования строки в дату с урезанной маской формата, поэтому, с точки зрения синтаксиса, запрос верный и выполнится успешно. Более важным вопросом является результат его выполнения, т.е. какую дату он вернет, если задан только день? Перед ответом на данный вопрос давайте вспомним, как Oracle устанавливает время, если оно явно не задано:

В запросе №4 время не указано, в запросе №5 указано только количество минут, часы и секунды опущены. В Oracle существует правило, согласно которому, если в дате отсутствует временной компонент, то время автоматически устанавливается в значение 00:00:00 (т.е. полночь), если задана только часть элементов времени (как в запросе №5), то пропущенные элементы устанавливаются в 00. Поэтому, запрос №4 вернет строку ‘03.02.2011 00:00:00’, а запрос №5 — ‘03.02.2011 00:30:00’.

Вернемся к запросу №3, верно ли данное правило для дат, т.е. заменяются ли пропущенные при конвертации элементы даты на 00 или 01? Да заменяются, но не все, точнее, для пропущенных элементов даты используются значения из sysdate (первый день текущего месяца текущего года). Поэтому запрос №3 будет использовать 09 в качестве месяца и 2011 в качестве года, таким образом, результатом выполнения запроса будет дата 03.09.2011.

Пример №2. Порядок параметров форматирования
Выполнится ли следующий запрос, и если да, то какую дату он вернет?

На первый взгляд, отсутствие разделителей в строке с датой может показаться критическим фактором несовместимым с выполнением запроса, однако маска даты также задана без разделителей и строка для преобразования соответствует указанному шаблону. Поэтому запрос №6 выполнится успешно и вернет 20.11.2009 (формат результата может несколько отличаться в зависимости от настроек сессии). Детальнее вопросы, связанные с разделителями, мы рассмотрим в следующем примере.

Пример №3. Неявная конвертация
Пусть формат даты по умолчанию DD.MON.RR, а язык даты – русский, отработает ли следующий запрос:

В данном запросе указано два строковых параметра, которые должны быть преобразованы в даты с помощью неявной конвертации. Согласно документации, при использовании форматов по умолчанию, строка для неявного преобразования в дату должна удовлетворять шаблону: [D|DD] separator1 [MM|MON|MONTH] separator2 [R|RR|YY|YYYY]. В качестве separator1 и separator2 можно использовать большинство разделительных знаков и специальных символов, в том числе пробелы, табуляцию, а также " и удвоенную одинарную кавычку ». Более того, если в строке указано не менее двух цифр для задания дней, месяцев и лет, то separator вообще может быть опущен. Например:

Поскольку обе строки указанные в запросе №7 соответствуют приведенному шаблону, то запрос выполнится успешно и вернет число 11.

Пример №4. Параметры функции to_date
Пусть формат даты по умолчанию DD.MON.RR, а язык даты – русский, отработает ли следующий запрос:

Схожий запрос фигурировал в одном из обсуждений на странице ask Tom. Ловушка запроса в том, что мы пытаемся преобразовать дату (sysdate) в дату. Если бы запрос выглядел так:

То выполнение прошло бы успешно, и он вернул строку ’09/15/2011 23:00:11′. Однако функция to_date в качестве первого параметра ожидает строку поэтому, вначале происходит неявная конвертация даты в строку (что эквивалентно вызову to_char(sysdate) с маской по умолчанию). Результатом данной конвертации является строка ‘15.09.11’, далее происходит вызов to_date. Таким образом, запрос №11 эквивалентен следующему запросу:

Как не сложно убедиться, запрос №13 не может быть выполнен, поскольку строка ‘15.09.11’ не соответствует установленной маске, соответственно, запрос №11 так же не может быть выполнен.

Установка формата даты по умолчанию
Формат дат по умолчанию задается двумя параметрами: NLS_DATE_FORMAT (отвечает за сам формат как таковой) и NLS_DATE_LANGUAGE (отвечает за язык, который будет использован при написании названий дней, месяцев и т.д.). Если эти параметры не заданы явно, то их значения устанавливаются на основе параметра NLS_LANG.

1. Уровень БД: Параметры этого уровня устанавливаются при создании БД и прописываются в файле init.ora.
2. Уровень экземпляра: Параметры этого уровня устанавливаются при старте экземпляра и могут быть изменены с помощью команды ALTER SYSTEM.
3. Уровень сессии: Параметры этого уровня могут быть изменены командой ALTER SESSION. Также значение данных параметров можно проверить с помощью запроса:

Логично предположить, что преобразование строки ‘11.09.11’ в дату пройдет успешно, а строки ’11.SEP.11′ – нет. Однако это не так, успешно выполнятся оба преобразования. Вначале я предполагал, что в случае невозможности преобразовать строку по маске сессии Oracle пытается задействовать маски других уровней (маска уровня БД у меня установлена в ‘DD-MON-RR’). Чтение документации показало, что это не так, и Oracle руководствуется принципами, описанными в предыдущем пункте.

Попробуем другой пример:

Если вы думаете, что результат будет идентичен предыдущему запросу, то вы ошибаетесь. Одно из преобразований не выполнится. В данном случае строка ‘11.09.11’ не удовлетворяет шаблону. Возможно, это мистика?

Увы, нет. Чтение документации показало, что существуют правила автозамены элементов форматирования даты. Ниже привожу таблицу замен.

Original Format Element	Additional Format Elements to Try in Place of the Original
‘MM’	‘MON’ and ‘MONTH’
‘MON	‘MONTH’
‘MONTH’	‘MON’
‘YY’	‘YYYY’
‘RR’	‘RRRR’

Глядя на содержимое этой таблицы, становится понятно, что в формате ‘DD.MM.RR’ неявным образом присутствует формат ‘DD.MON.RR’ (а также ‘DD.MONTH.RR’ и другие), а вот в формате ‘DD.MON.RR’ формат ‘DD.MM.RR’ не присутствует, что и объясняет поведение запросов №14 и №15.

Пример №6. Формат RR vs YY
Большинству пользователей отличия масок RR и YY хорошо известны, однако есть и те, кому данная информация окажется полезной. Перейдем сразу к рассмотрению примера. Какие данные вернут следующие запросы:

Оба приведенных выше запроса выполнятся успешно и вернут даты в соответствии с правилами, описанными в примере №1 для запроса №3. Таким образом, значение дня во всех полученных датах будет равно 01, а значение месяца 09 (если вы выполняете запрос в сентябре). Главный вопрос, каким будет значение года?

Как несложно предположить, в запросе №16 под ’11’ я подразумевал 2011 год и обе маски мне его вернули, т.е. результат выполнения запроса №16 это 01.09.2011 и 01.09.2011.

В запросе №17 под ’99’ я подразумевал 1999 год и тут мнения масок разделились: маска RR вернула ожидаемый 1999 год, а маска YY – 2099, т.е. результат выполнения запроса №17 это 01.09.1999 и 01.09.2099.

Рассмотрим, как работают эти элементы форматирования более детально:

• If the last two digits of the current year are 00 to 49, then the returned year has the same first two digits as the current year.
• If the last two digits of the current year are 50 to 99, then the first 2 digits of the returned year are 1 greater than the first 2 digits of the current year.

• If the last two digits of the current year are 00 to 49, then the first 2 digits of the returned year are 1 less than the first 2 digits of the current year.
• If the last two digits of the current year are 50 to 99, then the returned year has the same first two digits as the current year.

Поэтому запрос №19 вернет 1950 год в обоих случаях.

Пример №7. Некоторые другие примеры
В завершение обзора рассмотрим немного экзотики. Будет ли ошибка в результате выполнения следующего запроса:

Если вы решили, что это бессмысленная запись, то вы ошибаетесь – это вполне корректное задание даты в соответствии со стандартом ANSI, запрос №20 выполнится успешно и вернет 25.12.1928.

Какой из запросов не выполнится?

Данный пример призван продемонстрировать наличие третьего параметра в функции to_date. Данный параметр позволяет установить значение одного из NLS (National Language Support) параметров только для этого вызова функции to_date. Установку NLS параметров для сессии мы рассматривали в примере №5. Ключевая разница запросов №20 и №21 состоит не в названии месяца (маска MON автоматически замещается маской MONTH, как это описано в примере №5), а в указании разных языков даты. Запрос №21 ожидает название месяца на английском и, соответственно, не выполнится, запрос №22 ожидает название месяца на русском и выполнится успешно.

В каком случае следующее объявление таблицы может приводить к возникновению ошибок при вставке данных?

Каждый раз, когда сессия с форматом даты по умолчанию ‘DD.MON.RR’ будет производить вставку с указанием только значения первого столбца будет возникать ошибка.

Особенности отображения дат в различных приложениях

Что влияет на отображение даты
Этот раздел добавлен после публикации статьи благодаря рекомендациям, указанным в комментариях. Описанное далее верно как для отображения дат, так и для отображения чисел. Возможно, при выполнении некоторых приведенных выше в статье примеров, вы получили даты в отличном от указанного в результатах формате. Если настройки вашей сессии соответствовали указанным в примерах, то это представляется, по меньшей мере, странным.

Правда заключается в том, что при выполнении запроса
вы получаете дату, но для отображения результата на экран конкретная утилита, с помощью которой вы обращаетесь к БД, должна провести конвертацию даты в строку. Таким образом, для отображения дат (и чисел) неявным образом вызывается to_char, т.е. имеем классический случай неявной конвертации (это конвертация только для вывода на экран, ее результаты не участвуют ни в каких вычислениях и ни на что кроме отображения данных не влияют). Если есть неявная конвертация, значит, существует и маска, по которой она выполняется. В классическом случае это должна быть маска, установленная для сессии, т.е. маска, указанная в параметре NLS_DATE_FORMAT таблицы nls_session_parameters, с которой мы активно работали на протяжении всей статьи.

Давайте проверим работу некоторых приложений. Проверять будем с помощью следующего скрипта:

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

Рис. 1. Результат выполнения запроса №25 в sqlplus.

Как видно из рис.1, формат отображения даты меняется в зависимости от настроек сессии, т.е. sqlplus использует настройки сессии. Это упрощает понимание процесса преобразования дат в строки и обратно, поскольку и для преобразования и для отображения используются одинаковые маски.

Некоторые продвинутые средства разработки используют свои собственные NLS настройки, не имеющие отношения к настройкам Оракл. В качестве примера проверим, какие параметры для отображения дат использует PL/SQL Developer. Для этого выполним в нем запрос №25.

Рис. 2. Результат выполнения запроса №25 в PL/SQL Developer.

Как видно из рис.2, формат отображения даты не меняется при изменении настроек сессии. Более того, если посмотреть внимательно, то видно, что и первый и второй результаты вывода даты на экран не соответствовали параметрам сессии (в первом случае выведенная дата имела год в четырехзначном формате, а маска указывала год в двухзначном формате). Это означает, что утилита использует собственные NLS настройки, в случае PL/SQL Developer’а их расположение указано на рис. 3.

Рис. 3. Установка NLS параметров отображения дат в PL/SQL Developer.

Чем могут быть вредны NLS настройки утилит
Отображение даты в формате отличном от формата сессии вредно по одной причине – оно вводит пользователя в заблуждение и может привести к возникновению ошибок. Выполним в sqlplus и PL/SQL Developer следующий запрос:
В последнюю строку запроса вместо ХХХХХХХХ мы будем вставлять полученные из предыдущей строки данные.

Результаты выполнения запроса представлены на рисунках ниже.

Рис. 4. Результат выполнения запроса №26 в sqlplus.

Рис. 5. Результат выполнения запроса №26 в PL/SQL Developer.

Почему в sqlplus выведенные на экран данные были успешно конвертированы в дату, а данные выведенные на экран PL/SQL Developer’ом не смогли сконвертироваться? Потому что для конвертации Оракл использует формат данных указанный в сессии, а данные выведенные PL/SQL Developer’ом были приведены для отображения в свой формат, отличный от формата сессии.

Заключение

В качестве заключения хочу напомнить, что почти в каждом своем посте посвященном работе с датами, Том Кайт говорит о необходимости использования явных преобразований и обязательном указании маски. «При конвертации строки в дату никогда не полагайтесь на формат даты по умолчанию, всегда явно задавайте маску» — примерно так звучат его слова. Дополнительные примеры и возможные ошибки при работе с преобразованием дат вы можете найти, воспользовавшись страничкой ask Tom.

Поскольку работа с датами заняла всю статью, то «за бортом» осталось множество интересных вопросов, которые я хотел бы рассмотреть. Скорее всего, появится и третья часть статьи, как только у меня найдется свободное время.

Как указать формат даты в sql

DATE_FORMAT() function in MySQL is used to format a specified date as given format value i.e., a date will be given and this function will format that date as specified format parameters.

Syntax :

Parameter : This function accepts two parameters as given below :

• date – Specified date to be formatted.
• format – Specified format. This list of formats used in this function are listed below:

Returns :
It returns the formatted date.

Example-1 :
Getting a formatted year as “2020” from the specified date “2020-11-23”.

Output :

Example-2 :
Getting a formatted month name as “November” from the specified date “2020-11-23”.

Output :

Example-3 :
Getting a day of the month as a numeric value as “23rd” from the specified date “2020-11-23”.

Output :

Example-4 :
Getting month day and year as “November 23 2020” from the specified date “2020-11-23”.

Output :

Example-5 :
Getting hour and minute as “12 09” from the specified date and time “2020-11-23 12:09:23”.

Output :

Application : This function is used to format a specified date as given format value.

MySQL DATE_FORMAT Function

Summary: in this tutorial, you will learn how to use the MySQL DATE_FORMAT function to format a date value based on a specific format.

Introduction to MySQL DATE_FORMAT function

To format a date value to a specific format, you use the DATE_FORMAT function. The syntax of the DATE_FORMAT function is as follows:

The DATE_FORMAT function accepts two arguments:

• date : is a valid date value that you want to format
• format : is a format string that consists of predefined specifiers. Each specifier is preceded by a percentage character ( % ). See the table below for a list of predefined specifiers.

The DATE_FORMAT function returns a string whose character set and collation depend on the settings of the client’s connection.

The following table illustrates the specifiers and their meanings that you can use to construct a date format string:

Specifier	Meaning
%a	Three-characters abbreviated weekday name e.g., Mon, Tue, Wed, etc.
%b	Three-characters abbreviated month name e.g., Jan, Feb, Mar, etc.
%c	Month in numeric e.g., 1, 2, 3…12
%D	Day of the month with English suffix e.g., 0th, 1st, 2nd, etc.
%d	Day of the month with leading zero if it is 1 number e.g., 00, 01,02, …31
%e	Day of the month without leading zero e.g., 1,2,…31
%f	Microseconds in the range of 000000..999999
%H	Hour in 24-hour format with leading zero e.g., 00..23
%h	Hour in 12-hour format with leading zero e.g., 01, 02…12
%I	Same as %h
%i	Minutes with leading zero e.g., 00, 01,…59
%j	Day of year with leading zero e.g., 001,002,…366
%k	Hour in 24-hour format without leading zero e.g., 0,1,2…23
%l	Hour in 12-hour format without leading zero e.g., 1,2…12
%M	Full month name e.g., January, February,…December
%m	Month name with leading zero e.g., 00,01,02,…12
%p	AM or PM, depending on other time specifiers
%r	Time in 12-hour format hh:mm:ss AM or PM
%S	Seconds with leading zero 00,01,…59
%s	Same as %S
%T	Time in 24-hour format hh:mm:ss
%U	Week number with leading zero when the first day of week is Sunday e.g., 00,01,02…53
%u	Week number with leading zero when the first day of week is Monday e.g., 00,01,02…53
%V	Same as %U; it is used with %X
%v	Same as %u; it is used with %x
%W	Full name of weekday e.g., Sunday, Monday,…, Saturday
%w	Weekday in number (0=Sunday, 1= Monday,etc.)
%X	Year for the week in four digits where the first day of the week is Sunday; often used with %V
%x	Year for the week, where the first day of the week is Monday, four digits; used with %v
%Y	Four digits year e.g., 2000 and 2001.
%y	Two digits year e.g., 10,11,and 12.
%%	Add percentage (%) character to the output

The following are some commonly used date format strings:

DATE_FORMAT string	Formatted date
%Y-%m-%d	2013-07-04
%e/%c/%Y	4/7/2013
%c/%e/%Y	7/4/2013
%d/%m/%Y	4/7/2013
%m/%d/%Y	7/4/2013
%e/%c/%Y %H:%i	4/7/2013 11:20
%c/%e/%Y %H:%i	7/4/2013 11:20
%d/%m/%Y %H:%i	4/7/2013 11:20
%m/%d/%Y %H:%i	7/4/2013 11:20
%e/%c/%Y %T	4/7/2013 11:20
%c/%e/%Y %T	7/4/2013 11:20
%d/%m/%Y %T	4/7/2013 11:20
%m/%d/%Y %T	7/4/2013 11:20
%a %D %b %Y	Thu 4th Jul 2013
%a %D %b %Y %H:%i	Thu 4th Jul 2013 11:20
%a %D %b %Y %T	Thu 4th Jul 2013 11:20:05
%a %b %e %Y	Thu Jul 4 2013
%a %b %e %Y %H:%i	Thu Jul 4 2013 11:20
%a %b %e %Y %T	Thu Jul 4 2013 11:20:05
%W %D %M %Y	Thursday 4th July 2013
%W %D %M %Y %H:%i	Thursday 4th July 2013 11:20
%W %D %M %Y %T	Thursday 4th July 2013 11:20:05
%l:%i %p %b %e, %Y	7/4/2013 11:20
%M %e, %Y	4-Jul-13
%a, %d %b %Y %T	Thu, 04 Jul 2013 11:20:05

MySQL DATE_FORMAT examples

Let’s take a look at the orders table in the sample database.

To select the order’s data and format the date value, you use the following statement:

We formatted the order date, required date, and shipped date of each order based on different date formats specified by the format strings.

MySQL DATE_FORMAT with ORDER BY

See the following example:

In the query, we selected all orders whose shipped date are not NULL and sorted the orders by the shipped date. However, the orders were not sorted correctly.

The reason is that we used shippeddate as the alias for the output of the DATE_FORMAT function, which is a string, the ORDER BY clause took the alias and sorted the orders based on string values, not date values.

To fix this problem, we have to use an alias that is different from the column name; see the following statement:

In this tutorial, we have shown you how to use the MySQL DATE_FORMAT function to format the date based on a specified format.