I always seem to be digging around to find MySQL’s date formatting syntax, so here is a couple of common conversions…
select date_format(date, '%d %M %Y') as new_date from tablename
where date is the name of your date field, and new_date is the variable name which you can use to reference the value.
| date_format String | Example |
| ‘%e/%c/%Y’ | 25/4/2009 |
| ‘%c/%e/%Y’ | 4/25/2009 |
| ‘%d/%m/%Y’ | 25/04/2009 |
| ‘%m/%d/%Y’ | 04/25/2009 |
| ‘%a %D %b %Y’ | Fri 25th Apr 2009 |
A more complete list of specifiers is available here.
Here is a good website for formatting dates using the MySQL date_format function http://www.mysqlformatdate.com
The above post is correct, – quite a nifty little website (with a single unobtrusive advertisment) designed to assist quickly and easily with the construciton of your SQL query.
Derick Rethans has an amazing post on date/times mainly relating to PHP:
http://derickrethans.nl/storing-date-time-in-database.html
In it he explains what is necessary for true date and time comparisions, and why simply storing a UTC offset is not actually enough.