0

In my database, there are some liveDates that are 0000-00-00 but when I run mysqli query and display the results, the liveDate is displaying 11-30--0001,

this only started happening when I introduced this code to display the dates as d-m-Y 

<td>".date("m-d-Y",strtotime($row["liveDate"]))."</td>

PHP

echo "<tr><th>ID</th><th>Amount</th><th>Date</th><th>Method</th><th>Status</th><th>Live Date</th><th>Edit</th></tr>";

// output data of each row
while($row=mysqli_fetch_assoc($depositresult)) {


    echo "<tr><td>".$row["id"]."</td>
    <td>&pound;".$row["amount"]."</td>
    <td>".date("m-d-Y",strtotime($row["date"]))."</td>
    <td>".$row["method"]."</td>
    <td>".$row["status"]."</td>
    <td>".date("m-d-Y",strtotime($row["liveDate"]))."</td>
    <td><a href='edit-deposit.php?GetID=".$row['id']."'>Edit</a></td></tr>";
}

// table footer

echo "</table>";

it should be returning the liveDate as 00-00-0000 as the liveDate in the database table is 0000-00-00

if there is a date e.g. 2019-03-11 it works fine

enter image description here

Dharman
  • 30,962
  • 25
  • 85
  • 135
George Richardson
  • 81
  • 8
  • I just really would like to point out that the date '0000-00-00' doesn't exist. If the date is undefined it should be set to **null**. In fact if you enable strict mode in your DB you should not be able to store incorrect dates like this. https://stackoverflow.com/questions/36374335/error-in-mysql-when-setting-default-value-for-date-or-datetime – Dharman Mar 13 '19 at 00:02

1 Answers1

3

The strtotime function tries to interpret your date string. Since "0000-00-00" is actually an invalid date (there is no 0th month or a 0th day in any month) it tries to correct it.

Since day 0 is one day less than the first day of the month, it will jump to the last day of the previous month. Same with month 0, that's one less than the first month so it will become the last month of the previous year.

That's why "year 0, month 0, day 0" becomes "year -1, month 12, day 0" and then "year -1, month 11, day 30". Or, in "m-d-Y" format: "11-30--0001".

Update

If you want to keep the "00-00-0000" notation for these invalid dates, you cannot use the date formatting functions. The easiest method is to check for it and write it yourself:

echo "<tr><td>".$row["id"]."</td>
<td>&pound;".$row["amount"]."</td>
<td>".date("m-d-Y",strtotime($row["date"]))."</td>
<td>".$row["method"]."</td>
<td>".$row["status"]."</td>
<td>";

if ($row["liveDate"] == "0000-00-00") {
    echo "00-00-0000";
} else {
    echo date("m-d-Y",strtotime($row["liveDate"]));
}

echo "</td>
<td><a href='edit-deposit.php?GetID=".$row['id']."'>Edit</a></td></tr>";
rickdenhaan
  • 10,857
  • 28
  • 37