DATE_FORMAT within a query in CodeIgniter using Active Record don't work

Question

coders. I'm running a little problem here and can't find the solution. I'm building a query using Active Record from CI. This is the code for the query:

 $this->db->select("u.id AS user_id, u.email, p.display_name, p.first_name, p.last_name, s.status_id, s.message");
    $this->db->select("DATE_FORMAT(s.created_at, `Publicado el %d/%m/%Y a las %h:%i %p`) AS created_at", FALSE);

 $this->db->join($this->_table_users . ' u', 'u.id = s.user_id');
 $this->db->join($this->_table_profiles . ' p', 'p.user_id = u.id');

 $this->db->where_in('user_id', $str);
 $this->db->where('deleted', 0);

 $this->db->from($this->_table . ' s');

 $this->db->order_by('s.created_at', 'desc');

But I'm getting this error:

Error Number: 1054

Unknown column 'Publicado el %d/%m/%Y a las %h:%i %p' in 'field list'

SELECT u.id AS user_id, u.email, p.display_name, p.first_name, p.last_name, s.status_id, s.message, DATE_FORMAT(s.created_at, Publicado el > %d/%m/%Y a las %h:%i %p) AS created_at FROM (default_status s, default_status) JOIN > default_users u ON u.id = default_s.user_id JOIN default_profiles p ON p.user_id = u.id WHERE user_id IN ('5726, 2, 10293') AND deleted = 0 ORDER BY s.created_at desc LIMIT 2

Does any know where I can use DATE_FORMAT within this query? The funny is the same query write as pure SQL works Cheers and thanks in advance

Answer 1

Add FALSE as the second parameter in your SELECT statement. You may have to do something similar in your WHERE clause as well.

Some links to look over:

http://ellislab.com/forums/viewthread/74206/#368778

http://ellislab.com/forums/viewthread/206962/#963061

http://ellislab.com/codeigniter/user-guide/helpers/date_helper.html

Answer 2

Rectify your select

$this->db->select("u.id AS user_id, u.email, p.display_name, p.first_name, p.last_name, s.status_id, s.message");
$this->db->select("DATE_FORMAT(s.created_at, `%d/%m/%Y`) AS created_at", FALSE);

$this->db->join($this->_table_users . ' u', 'u.id = s.user_id');
$this->db->join($this->_table_profiles . ' p', 'p.user_id = u.id');

$this->db->where_in('user_id', $str);
$this->db->where('deleted', 0);

$this->db->from($this->_table . ' s');

$this->db->order_by('s.created_at', 'desc');

Answer 3

A trick that works for me is to put the date format string inside parenthesis. For example replacing

DATE_FORMAT(`pf_Partido`.`fecha`,'%d-%m-%Y')

by

DATE_FORMAT(`pf_Partido`.`fecha`,('%d-%m-%Y'))

In your case the code would be

$this->db->select("DATE_FORMAT(s.created_at, ('Publicado el %d/%m/%Y a las %h:%i %p')) AS created_at");

--Better late than never

Answer 4

Look at the returning message, the format 'Publicado el > %d/%m/%Y a las %h:%i %p' can't be passed with `, codeigniter removes it, you need to use the ' (single quotes) to delimit them. for more information see: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format