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 ?

If you sometimes don't enter a date into the field, make it NULL. — deceze
– deceze ♦, Commented Jun 4, 2013 at 10:31
@Stephan There is way more that that is supported php.net/manual/en/datetime.formats.php — PeeHaa
– PeeHaa, Commented Jun 4, 2013 at 10:34

BlitZ · Accepted Answer · 2013-06-04 10:52:57Z

3

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'

edited Jun 4, 2013 at 10:52

answered Jun 4, 2013 at 10:35

BlitZ

12.2k3 gold badges52 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BlitZ Over a year ago

@ShivanRaptor NULL is more easy to check though. You need more?

michaelb958--GoFundMonica · Accepted Answer · 2013-06-04 10:53:23Z

1

$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;

edited Jun 4, 2013 at 10:53

michaelb958--GoFundMonica

4,7367 gold badges34 silver badges35 bronze badges

answered Jun 4, 2013 at 10:33

ankit

864 bronze badges

Collectives™ on Stack Overflow

DateTime object creation on MySQL non null field

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related