CODEWITHSUNDEEP
phpmysqlcodeigniterdate
a7omiton
a7omiton

Reputation: 1617

MySQL returning -0001 as year

I'm trying to store a date that is converted and displayed as one format (d M, Y) to the user and stored in the default format in the mysql table (yyyy-mm-dd).

I'm storing the date using the following function on each date field:

private function formatDate($date)
    {
        if(!empty($date))
        {
            $changeFormat = DateTime::createFromFormat('d M, yy', $date);
            $newDate = $changeFormat->format('Y-m-d');
        } else {
            return "";
        }
        return $newDate;
    }

and reverting it back to d M, yy with the following function:

private function formatDateBack($date)
    {
        if(!empty($date))
        {
            $changedFormat = DateTime::createFromFormat('Y-m-d', $date);
            $newDate = $changedFormat->format('d M, Y');
        } else {
            return "";
        }
        return $newDate;
    }

When I use the above function to retrieve the date and show it to the user, and the date field is empty, I get the following date back:

30 Nov, -0001

I have a hunch that this is because of the two different year formats given to the format() function above in my two private functions, the first being 'd M, yy' and the second being 'd M, Y'. However, this is the way that I can properly change the date formats, because if I change the format to 'd M, yy' in formatDateBack(), I will get a date like '4 Jan, 1414'.

I don't understand why I'm getting the date 30 Nov, -0001 back, even when the date shown in the database table is 0000-00-00. I would even appreciate it if I got that back instead!

If someone can help me I would love to find a solution to this.

EDIT: my date fields data type is date

Upvotes: 2

Views: 940

Answers (2)

user8675
user8675

Reputation: 686

public function formatDateBack($dtm) 
{       
    if(is_null($dtm) || $dtm == '0000-00-00') return 'whatEverYouLike';
    $datum = new DateTime($dtm);
    return $datum->format('d.m.Y');
}

Upvotes: 3

Alex Siri
Alex Siri

Reputation: 2864

You are getting this result, probably, because of the time zone. If the date is null, it's being translated to 0, which is 1 of Jan of year 0, in GMT. In a different time zone, that time changes slightly.

I can only think that you're getting 30 of Nov instead of 31 Dec because this conversions, while converting the fields, lead to some problems when the month indexes change from 0-based to 1-based.

Upvotes: 0

Related Questions