Search code examples
sqlformatdatetime

problems date_format date change, add minutes

I have a problem. I do a query like this:

"SELECT nome, DATE_FORMAT(data,'%H:%m') as ora_inizio"

If I have in the database a dete: 2013-08-01 16:00:00 when I show the dates the date is: 2013:08_01 16:02:00 How is possible?

Solution

  • I'm assuming you're using MySQL %m is Month where 1 is January. For minutes you need to use %i.

    SELECT nome, DATE_FORMAT(data,'%H:%i') as ora_inizio

    will show 06:00. To shown the full date and time use:

    SELECT nome, DATE_FORMAT(data,'%Y-%m-%d %H:%i:%s') as ora_inizio