DateTime object creation on MySQL non null field

Question

I have a MySQL table with a DATETIME NOT NULL data type (didn't specify default value).

For MySQL default, if I don't specify data, the value will become 0000-00-00 00:00:00.

Then I fetch the value from database, which value is 0000-00-00 00:00:00. Then I try to create a DateTime object with the retrieved data.

$date = new DateTime('0000-00-00 00:00:00');
$formatted_date = $date->format('Y-m-d');
echo $formatted_date; // which display -0001-11-30, an obvious invalid date

What is the best way to check if the value is not a valid date ?

Answer 1

Check column with NULLIF() function:

SELECT NULLIF('0000-00-00 00:00:01', '0000-00-00 00:00:00');
// Shows 0000-00-00 00:00:01

SELECT NULLIF('0000-00-00 00:00:00', '0000-00-00 00:00:00');
// Shows NULL

However, I suggest to apply what @deceze said:

If you sometimes don't enter a date into the field, make it NULL

Data should be stored in appropriate form. So I suggest to ALTER your table column to accept NULLs.

UPDv1:

If you don't need computations with dates in PHP, and need just to output it, I also would suggest to use DATE_FORMAT() as it is more reliable in this matter:

SELECT DATE_FORMAT('0000-00-00 00:00:00', '%d.%m.%Y %H:%i:%s');
// Shows '00.00.0000 00:00:00'

Answer 2

$formatted_date = "";
if($data['row'] != "0000-00-00 00:00:00")
{
  $date = new DateTime('0000-00-00 00:00:00');
  $formatted_date = $date->format('Y-m-d');
}
echo $formatted_date;