CODEWITHSUNDEEP
phpmysqlcodeigniterdatedatetime
Saswat
Saswat

Reputation: 12846

How to fetch Date in d-m-Y format from a DATETIME field in codeigniter active records?

I have a table called tbl_user where user_added_date is a DATETIME (Y-m-d H:i:s) type. I am trying to fetch all the data using the following active records:-

$this->db->select('user_name AS Name, user_username AS Username, 
                   user_mobile AS Mobile, 
                   DATE(user_added_date) AS `Joining Date`');       
$dataResult = $this->db->get('user');

Now, I am getting the data in mm/dd/YYYY format.

How can I fetch the data in dd-mm-YYYY format?

EDIT:-

After I updated the answer as stated by Mr. Gordon, I am facing the issue, which is more specific to codeigniter.

The updated active records is 

$this->db->select('user_name AS Name, 
                            user_username AS Username, 
                            user_mobile AS Mobile, 
                            DATE_FORMAT( user_added_date, "%d-%m-%Y" ) AS `Joining Date`,
                            DATE(user_last_activity) AS `Last Time Active`');       
$dataResult = $this->db->get('user');

Which is producing this error:-

Error Number: 1054

Unknown column '"%d-%m-%Y"' in 'field list'

SELECT `user_name` AS Name, `user_username` AS Username, `user_mobile` AS Mobile, DATE_FORMAT( user_added_date, `"%d-%m-%Y"` ) AS `Joining Date`, DATE(user_last_activity) AS `Last Time Active` FROM (`tbl_user`)

Filename: /var/www/html/custom/ci/tharjumal/controllers/admin/user.php

Line Number: 103

This is more due to the active records properties which is encapsulating the "%d-%m-%Y" within a (`) sign and thus treating it like a column.

How can I solve this issue?

Upvotes: 0

Views: 2071

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270443

You can use date_format() in the query:

$this->db->select('user_name AS Name, user_username AS Username, 
                   user_mobile AS Mobile, 
                   date_format(user_added_date, "%d/%m/%Y") AS `Joining Date`');

Upvotes: 3

Related Questions