MySQL select date with a format in codeigniter

Question

My Codeigniter site select date from a table as follows.

$this->db->select('reg_id, fname, lname, added_date');// working

MYSQL database has added_dateformat as 2016-09-10 14:44:46 Now i want to select data from table format should be 2016-09-10 14:44 PM

selecting date separately can get the format

SELECT DATE_FORMAT(added_date, '%Y-%m-%d %h:%i %p') FROM tble_reg

Then how to select other columns with added_date relevant format

$this->db->select('reg_id, fname, lname, DATE_FORMAT(added_date, '%Y-%m-%d %h:%i %p')'); 
//not working

Answer 1

This is speculation, but you might have the single quotes competing with each other. Does this work?

$this->db->select('reg_id, fname, lname, DATE_FORMAT(added_date, "%Y-%m-%d %h:%i %p") as added_date');

MySQL allows both single quotes and double quotes for string delimiters, which is handy in cases when single quotes are needed for delimiting strings at the application layer.

Answer 2

Brake the line in to two select statements

$this->db->select('reg_id, fname, lname');
$this->db->select(DATE_FORMAT(added_date, '%Y-%m-%d %h:%i %p') AS added_date);

Or use " with Date Format

$this->db->select('reg_id, fname, lname, DATE_FORMAT(added_date, "%Y-%m-%d %h:%i %p")  AS added_date ');