Change date format when adding where condition to the query

Question

Database:

   | date_paid (int) |
    1535558400
    1539532800

I am trying to query the following but i get SQL syntax error for the date part.

    $id = $this->input->post('id');
    $date = $this->input->post('date');

    $this->db->where('id',$id);
    $this->db->where('DATE_FORMAT(date_paid, "%Y-%m-%d")',$date);
    $query=$this->db->get('payments');
    $result=$query->row();

Is there any way to change the date format of the column date_paid so that i can actually compare it to the $this->input->post('date') data?

Error log:

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 ''2018-10-30'' at line 4

I am trying to modify an existing web application that is why as much as possible i do not want to change the data type of the column date_paid

thanks in advance

I have no suggested changing the datatype I just wanted to knwo what it was — RiggsFolly, Dec 18 '18 at 09:16
And the format of the input date from the user is also very relevant — RiggsFolly, Dec 18 '18 at 09:17
Show us which formats the user could enter the date in. One would hope only one format, but you cannot match a date to the date on the database unless you know what format the incoming date could be in — RiggsFolly, Dec 18 '18 at 09:31
the post('date') is actually `type='hidden'`. i am passing a string with this format `Y-m-d` — Ronald Torres, Dec 18 '18 at 09:36

Igor Carvalho · Answer 1 · 2018-12-18T09:53:07.143

0

Please, use like this

 DATE_FORMAT(FROM_UNIXTIME(`date_paid`), '%Y-%m-%d') AS 'date_changed'

You can refer here

edited Dec 18 '18 at 09:53

answered Dec 18 '18 at 09:12

Igor Carvalho

668
5
19

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 'FROM (payments) WHERE id = '28' AND date_formated = '2018-10-30' at line 2 – Ronald Torres Dec 18 '18 at 09:42
Did you refer my given link? – Igor Carvalho Dec 18 '18 at 09:51

marcogmonteiro · Answer 2 · 2018-12-18T09:56:42.683

0

You need to use

DATE_FORMAT(FROM_UNIXTIME(date_paid), '%Y-%m-%d') AS 'date_formatted'

so something like:

$id = $this->input->post('id');
$date = $this->input->post('date');

$this->db->select("DATE_FORMAT(FROM_UNIXTIME(`date_paid`), '%Y-%m-%d') AS date_formatted");
$this->db->from('payments');
$this->db->where("DATE_FORMAT(FROM_UNIXTIME(`date_paid`), '%Y-%m-%d') = " . $date, '', false);

$result = $q->row();

edited Dec 18 '18 at 09:56

answered Dec 18 '18 at 09:13

marcogmonteiro

2,061
1
17
26

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 'FROM (`payments`) WHERE `id` = '28' AND `date_formated` = '2018-10-30' at line 2 – Ronald Torres Dec 18 '18 at 09:31
@RonaldTorres Updated the query. – marcogmonteiro Dec 18 '18 at 09:57

score -2 · Answer 3 · answered Dec 18 '18 at 09:13

-2

Use the syntax below

select convert(varchar(4), year(ColumnDate))+'/'+convert(varchar(4), month(ColumnDate))+'/'+convert(varchar(4), day(ColumnDate)) from YourTable

answered Dec 18 '18 at 09:13

Paul Davies

97
2
9

Change date format when adding where condition to the query

3 Answers3