Date format in MySQL

Hi guys, today let’s talk about how to format date in MySQL
For this tutorial I’m using mysql server 5.5
Basically you need to know how to use a function called DATE_FORMAT
Let’s create a table and insert some rows:

CREATE TABLE IF NOT EXISTS `dates` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

INSERT INTO `dates` (`id`, `date`) VALUES
(1, '2012-10-17 13:39:55'),
(2, '2012-10-09 09:23:11'),
(3, '2012-08-24 16:47:07');

OK? then let’s select this values and show it formated

SELECT id, DATE_FORMAT( `date` , '%d/%c/%Y %H:%i:%s' ) AS `date` FROM `dates`

It’s easy, just remember which the format is always %ONE_LETTER, where this letter is the field which you want to show (see the complete documentation)

Liked? Help and Share!
This entry was posted in MySQL. Bookmark the permalink.
  • Carlos

    Boa tarde Marcelo, como posso fazer as informações ficarem em %d-%c-%Y definitivamente?

    • marceloaltmann

      Bom Dia Carlos.
      Infelizmente, tu não pode alterar o padrão como o MySQL grava e retorna as datas, porem, tu pode criar uma view para a tua tabela que faça a conversão automática das datas:


      CREATE TABLE `t1` (
      `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `Nome` varchar(150) DEFAULT NULL,
      `data` date DEFAULT NULL,
      PRIMARY KEY (`ID`)
      );
      CREATE VIEW `v_t1` AS select `t1`.`ID` AS `ID`,`t1`.`Nome` AS `Nome`,date_format(`t1`.`data`,'%d-%c-%Y') AS `data` from `t1`;

      INSERT INTO t1 VALUES (NULL, 'Data 1', '2014-01-01'), (NULL, 'Data 2', NOW());

      SELECT * FROM v_t1;
      +----+--------+-----------+
      | ID | Nome | data |
      +----+--------+-----------+
      | 1 | Data 1 | 01-1-2014 |
      | 2 | Data 2 | 07-6-2014 |
      +----+--------+-----------+

      Abraço.