« MySQL Pop Quiz #14 | Main | MySQL Function of the Day : The First Week »
DATE_FORMAT :: MySQL Function of the Day
By Carsten | March 11, 2008
Function name: DATE_FORMAT()
Aliases: -
Function type: Temporal function
Purpose: Display a date, time or datetime in a particular format; manipulate dates.
Description: DATE_FORMAT() at its simplest, returns a string containing a formatted date/time/datetime value. To use DATE_FORMAT(), provide it with a temporal value and a formatting string. A long list of formatting specifiers are available, which all start with “%”. For example, “%D” means “day of month with suffix”, so DATE_FORMAT(’2008-03-11′, ‘%D’) will return “11th”.
The formatting expression can be as simple or as complex as you like, and may include non-formatting strings as well. For example,
DATE_FORMAT(’2008-03-11 14:32;56′,
‘This text was written at %l:%i:%s%p on %W the %D of %M in the year %Y’
)
would return:
‘This text was written at 2:32:56PM on Tuesday the 11th of March in the year 2008′
DATE_FORMAT() can also, to some extent, be used to manipulate temporal data. For example, to display the first day of the current month, you can show the output of SELECT DATE_FORMAT(NOW() ,’%Y-%m-01′);
If you’re used to using PHPs DATE() function, note that DATE_FORMAT() DATE() take their arguments in reverse order. I personally find MySQLs date/time handling and formatting to be the more flexible of the two, and I usually do all my temporal arithmetic on the MySQL server unless it hurts performance.
<plug>Incidentally, I recently created a tool to make it easy to create formatting strings for both PHP and MySQL. You can find it right here.</plug>
MySQL manual entry on DATE_FORMAT()
[fadps]
Topics: MySQL Function of the day |