Getting syntax error when using mysql DATE_FORMAT

Question

I am using mysql DATE_FORMAT for a specific datetime in database. Mysql Query is:

$this->db->select('remarks_date, DATE('remarks_date') AS rem_dateonly, DATE_FORMAT('remarks_date', '%y-%m-%d %h:%i') AS day_hour_mins, DATE_FORMAT('remarks_date', '%y-%m-%d %h') AS day_hour, tbl_complain_detail.complain_summary, tbl_complain_detail.id, tbl_complain_detail.complain_no, tbl_complain_detail.created_by, escalation_status, tbl_admin_user.first_name, tbl_admin_user.middle_name, tbl_admin_user.last_name');
$this->db->where('tbl_complain_remarks.status', 'Time Extension');
$this->db->where('tbl_complain_detail.status !=', 'Completed');
$this->db->where('tbl_complain_detail.time_extension_status', '1');
$this->db->join('tbl_complain_remarks', 'tbl_complain_remarks.complain_id = tbl_complain_detail.id');
$this->db->join('tbl_admin_user', 'tbl_admin_user.id = tbl_complain_detail.user_id');
$result2 = $this->db->get('tbl_complain_detail')->result();

Here I am getting error as

PHP Parse error: syntax error, unexpected T_STRING in select line

and also it's not taking %. Why?

Am I doing something wrong? Any help/suggestions will be appreciated.

Try to echo the query so that you can see the constructed query. — Jenz
– Jenz, Commented May 16, 2014 at 5:32
Add DATE(remarks_date) without quotes if use quotes means it consider as a string — Ramki
– Ramki, Commented May 16, 2014 at 5:36

Hrishav Ojha · Accepted Answer · 2014-05-19 12:35:47Z

Your problem is with the alias in the table. For using alias in the table use `` symbol. Try this one.

SELECT remarks_date AS rem_date_only, DATE_FORMAT(remarks_date, "%Y-%m-%d %H:%i") AS `day_hour_mins`, DATE_FORMAT(remarks_date, "%Y-%m-%d %H") AS `day_hour`, tbl_complain_detail.complain_summary, tbl_complain_detail.id, tbl_complain_detail.complain_no, tbl_complain_detail.created_by, escalation_status, tbl_admin_user.first_name, tbl_admin_user.middle_name, tbl_admin_user.last_name
FROM (`tbl_complain_detail`)
JOIN `tbl_complain_remarks` ON `tbl_complain_remarks`.`complain_id` = `tbl_complain_detail`.`id`
JOIN `tbl_admin_user` ON `tbl_admin_user`.`id` = `tbl_complain_detail`.`user_id`
WHERE `tbl_complain_remarks`.`status` =  'Time Extension'
AND `tbl_complain_detail`.`status` != 'Completed'
AND `tbl_complain_detail`.`time_extension_status` =  '1'

Dinuka Thilanga · Accepted Answer · 2014-05-16 14:51:21Z

2

You use single quote with simple string. Use double quote for complex string.

$this->db->select("remarks_date, DATE(remarks_date) AS rem_dateonly, DATE_FORMAT(remarks_date, '%y-%m-%d %h:%i') AS day_hour_mins, DATE_FORMAT(remarks_date, '%y-%m-%d %h') AS day_hour, tbl_complain_detail.complain_summary, tbl_complain_detail.id, tbl_complain_detail.complain_no, tbl_complain_detail.created_by, escalation_status, tbl_admin_user.first_name, tbl_admin_user.middle_name, tbl_admin_user.last_name", false);

edited May 16, 2014 at 14:51

answered May 16, 2014 at 5:36

Dinuka Thilanga

4,35010 gold badges59 silver badges94 bronze badges

4 Comments

samjhana joshi Over a year ago

Asuraya and Manibharathi .. I am getting error as You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':%i) AS day_hour_mins

Dinuka Thilanga Over a year ago

OK. i change it. check it now.

samjhana joshi Over a year ago

Still I am getting error as Parse error: syntax error, unexpected '%'

Dinuka Thilanga Over a year ago

I add false value as params according to stackoverflow.com/questions/8125274/…. check it now

Manibharathi · Accepted Answer · 2014-05-16 05:49:38Z

0

Mysql doesn't support ' single quotes when specify column in select query so use this

DATE(`remarks_date`)

OR

DATE(remarks_date)

Instead of

DATE('remarks_date')

$this->db->select('remarks_date, DATE(`remarks_date`) AS rem_dateonly, DATE_FORMAT(`remarks_date`, "%y-%m-%d %h:%i") AS day_hour_mins, DATE_FORMAT(`remarks_date`, "%y-%m-%d %h") AS day_hour, tbl_complain_detail.complain_summary, tbl_complain_detail.id, tbl_complain_detail.complain_no, tbl_complain_detail.created_by, escalation_status, tbl_admin_user.first_name, tbl_admin_user.middle_name, tbl_admin_user.last_name');

edited May 16, 2014 at 5:49

answered May 16, 2014 at 5:43

Manibharathi

9456 silver badges18 bronze badges

1 Comment

Manibharathi Over a year ago

@sammy watch my updated answer. use DATE_FORMAT(remarks_date, "%y-%m-%d %h:%i") instead of DATE_FORMAT(remarks_date, %y-%m-%d %h:%i)

Collectives™ on Stack Overflow

Getting syntax error when using mysql DATE_FORMAT

3 Answers 3

Comments

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related