0

I have a string date of "July 14, 2018" from MySQL database. I just want to convert it to date format because I think my query doesn't recognize it. I have this sample code below:

$res=mysqli_query($conn,"SELECT SUM(price) as INCOME,DATE_FORMAT('%Y-%m',trans_date) as Date
                        from transactions where status='Paid' 
                        GROUP BY DATE_FORMAT('%Y-%m', trans_date)");

while($row=mysqli_fetch_array($res))
{
    $income = $row['INCOME'];
    $date = $row['Date'];

    $formattedincome = number_format($income, 2)."<br>";
    echo "₱".$formattedincome;
    echo $date;
}

When I tried to run the query on phpmyadmin, the Date column was NULL.

enter image description here

Hope it is possible. Thank you in advance.

Mr J.
  • 215
  • 1
  • 4
  • 12
ravendee
  • 1
  • 3
  • I would have to ask why are you storing a date on the database in a string and not in a DATE column. – RiggsFolly Jul 17 '18 at 14:15
  • 1
    Its null because you cannot use `DATE_FORMAT` on a non-DATE type field. Since you said its just a string (assuming varchar?). – IncredibleHat Jul 17 '18 at 14:15
  • I stored the date first in a `SESSION` variable to use on other page. Then on that page, the insertion will happen. I stored it like this: `$_SESSION[datenow] = date('F j, Y');` – ravendee Jul 17 '18 at 14:30

0 Answers0