How can I change the grammar in mysql?

user_17721300 0
Viewed 52

in my query

$stmt = $this->conn->prepare("SELECT p_index,m_id,DATE_FORMAT(trdDtTm, '%Y%m%d') as trdDtTm FROM ".$this->itemsTable." WHERE m_id = 2");$stmt->bind_param("i", $this->m_id);

result

{"items":[{"p_index":7,"m_id":"2","trdDtTm":"20220203"}}

However, if you search after changing the trdDtTm format, the results will not come out. How do I fix it?

$stmt = $this->conn->prepare("SELECT p_index,m_id,DATE_FORMAT(trdDtTm, '%Y%m%d') as trdDtTm FROM ".$this->itemsTable." WHERE m_id = 2 and trdDtTm = 20220203"); 
$stmt->bind_param("ii", $this->m_id, $this->trdDtTm);

no items.. how to fix it?

mysql sql php
1 Answers

trdDtTm is a datetime column.

WHERE trdDtTm = 20220203

compares a datetime with a number hence. This makes hardly sense. Maybe you hope for the DBMS to magically match the two, but you shouldn't. Compare with a date literal instead:

WHERE trdDtTm = DATE '2022-02-03'

And if you want to find all rows on that date regardless of the time (the above only finds midnight, as DATE '2022-02-03' is considered equal to TIMESTAMP '2022-02-03 00:00:00'), do:

WHERE trdDtTm >= DATE '2022-02-03' AND trdDtTm < DATE '2022-02-04'
user_22707620
Related