Функция MySQL DATE_FORMAT - параметры
Формат использования: DATE_FORMAT(date,’format’)
где date- поле БД, ‘format’ - формат вывода.
Описание поля 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 `%’ |
Пример DATE_FORMAT
SELECT DATE_FORMAT(`startdate`,'%m%Y') AS dateid, DATE_FORMAT(`startdate`,'%c') AS mdate, DATE_FORMAT(`startdate`,'%Y') AS ydate, COUNT(*) AS newsnumber
FROM news
GROUP BY mdate, ydate
ORDER BY `startdate` DESC
В этом примере результат в переменной dateid будет иметь вид 022008, а для этой же строки переменная mdate будет равна 2 - то есть тот же месяц, но без ведущих нулей.
ВАЖНО: В систаксисе функции php date() вместо %c используется %n - будьте внимательны!
21 Ноябрь 2009 в 3:19 пп
Спасибо!
Reply
25 Январь 2010 в 8:22 дп
Полезная заметка, спасибо!
Reply