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?
Advertisement
Answer
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'