Формат использования: DATE_FORMAT(date,'format')
где date- поле БД, 'format' - формат вывода.
как написать формат:
| %M | Month name (January..December) |
| %W | Weekday name (Sunday..Saturday) |
| %D | Day of the month with English suffix (1st, 2nd, 3rd, etc.) |
| %Y | Year, numeric, 4 digits |
| %y | Year, numeric, 2 digits |
| %X | Year for the week where Sunday is the first day of the week, numeric, 4 digits, used with '%V' |
| %x | Year for the week, where Monday is the first day of the week, numeric, 4 digits, used with '%v' |
| %a | Abbreviated weekday name (Sun..Sat) |
| %d | Day of the month, numeric (00..31) |
| %e | Day of the month, numeric (0..31) |
| %m | Month, numeric (01..12) |
| %c | Month, numeric (1..12) |
| %b | Abbreviated month name (Jan..Dec) |
| %j | Day of year (001..366) |
| %H | Hour (00..23) |
| %k | Hour (0..23) |
| %h | Hour (01..12) |
| %I | Hour (01..12) |
| %l | Hour (1..12) |
| %i | Minutes, numeric (00..59) |
| %r | Time, 12-hour (hh:mm:ss [AP]M) |
| %T | Time, 24-hour (hh:mm:ss) |
| %S | Seconds (00..59) |
| %s | Seconds (00..59) |
| %p | AM or PM |
| %w | Day of the week (0=Sunday..6=Saturday) |
| %U | Week (0..53), where Sunday is the first day of the week |
| %u | Week (0..53), where Monday is the first day of the week |
| %V | Week (1..53), where Sunday is the first day of the week. Used with '%X' |
| %v | Week (1..53), where Monday is the first day of the week. Used with '%x' |
| %% | A literal `%'. |
Пример:
SELECT *,DATE_FORMAT(date_registration,'%d.%m.%Y') as date_reg FROM users
даты будут в следующем формате: 04.09.2007 и будут находиться в поле date_reg, не date_registration (в возвращаемом массиве будет новое поле date_reg)