Mart Haarman Mart Haarman - 1 year ago 59
MySQL Question

php changed database date value

review this piece of code:

$values = $mysqli->query("select * from `my_table`");
while($value = $values->fetch_array()) {

however one column having a date structure does this:

[8] => 2016-11-20
[date] => 2016-10-02 15:52:18

As you can see, 8 is having a different value as date however, they should be the same value because it is the same column in the same row.
How can 'date' be changed?

the expected result = 2016-11-20 because that is the same value as stored in my database

info about my table:
it has 8 different columns containing all kind of things that do work. the 8th field is named 'date' and has type 'DATE'

concluding: somehow my output is in datetime (wrong time and date though) while it should be DATE

Answer Source

Depening on your version of MySQL, there seems to be some ambiguity (to me at least) about the "DATE" column type. From your results it appears that DATE is an alias for DATETIME, but MySQL does some truncation on the result.

So, a work around.. Don't user * , instead specify your column names , and on column 8, use the DATE function to retireve only the date portion.

select `col1`, `col2`, ..., DATE(`col8`) from `my_table`");
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download