Adding One Hour In DATE_FORMAT

Question

Hi Friends i am new one to MySQL I want display my date time format as like the following that is one hour to next hour from DATE_FORMAT() function using MySQL

2015-01-21 12:TO:01:AM

To display for above format i am using the following MySQL query

SELECT DATE_FORMAT(date_time,'%Y-%m-%d %h:TO:(%h+1):%p') from table

but i am getting the following output

2015-01-21 12:TO:(12+1):AM

please give me your guidance

Answer 1

You need to add an hour to the date, and format that time separately so you can concatenate the two strings.

SELECT CONCAT(DATE_FORMAT(date_time, '%Y-%m-%d %h:TO:'),
              DATE_FORMAT(DATE_ADD(date_time, interval 1 hour), '%h'),
              DATE_FORMAT(date_time, ':%p'))
FROM table