Insert date value in Mysql DB

Question

I'm trying to insert a date value in a MySQL table like this (using Laravel 5.1):

public function update(Request $request, $id)
{
  $user = User::findOrFail($id);      
  $data = $request->all();         
  $data['birthdate'] = date("Y-d-m", strtotime($data['birthdate']));
  ...
  $user->fill($data)->save();

It works fine if I insert a date which the day < 12, while it'll be inserted 1970-01-01 if day > 12 !

I've used Eloquent mutators in User model like this :

public function getBirthdateAttribute() {
    return date('d/m/Y', strtotime($this->attributes['birthdate']));        
}

public function setBirthdateAttribute($value) {              
    $this->attributes['birthdate'] = Carbon::createFromFormat('Y-m-d', $value);
}

Please what's the matter with my code !?

I enter date value like this : dd/mm/YYYY but it works fine if I enter a date which day<12 !!! — BKF
– BKF, Commented Jan 11, 2016 at 20:57
But the format strtotime understands is mm/dd/yyyy or yyyy/mm/dd, see docs.php.net/manual/en/datetime.formats.date.php — VolkerK
– VolkerK, Commented Jan 11, 2016 at 20:58

Gouda Elalfy · Accepted Answer · 2016-01-11 20:59:24Z

2

change this:

date("Y-d-m", strtotime($data['birthdate']));

to:

date("Y-m-d", strtotime($data['birthdate']));

http://dev.mysql.com/doc/refman/5.7/en/date-and-time-literals.html

where your should be like this format dd-mm-yyyy, and if it like this dd/mm/yyyy

$date = '22/05/2012';
$date = str_replace('/', '-', $date);
$date = date('Y-m-d', strtotime($date));

edited Jan 11, 2016 at 20:59

answered Jan 11, 2016 at 20:47

Gouda Elalfy

7,0833 gold badges28 silver badges38 bronze badges

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

3 Comments

BKF Over a year ago

Same issue .. :/ If i insert for example 01/03/1990 I'll get 03/01/1990 and if 13/01/1990 will be 1970-01-01 ... :(

VolkerK Over a year ago

13/01/1990 - that would be the first day of the 13th month in 1990. see docs.php.net/manual/en/datetime.formats.date.php

BKF Over a year ago

Thank you so much !! it works !! :))))))) $date = '22/05/2012'; $date = str_replace('/', '-', $date); $date = date('Y-m-d', strtotime($date));

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

This is the piece of the strtotime() manual you need to read and understand

Note:

Dates in the m/d/y or d-m-y formats are disambiguated by looking at the separator between the various components: if the separator is a slash (/), then the American m/d/y is assumed; whereas if the separator is a dash (-) or a dot (.), then the European d-m-y format is assumed. If, however, the year is given in a two digit format and the separator is a dash (-, the date string is parsed as y-m-d.

To avoid potential ambiguity, it's best to use ISO 8601 (YYYY-MM-DD) dates or DateTime::createFromFormat() when possible.

So because you are using the / as a seperator strtotime() is assuming the American date format and getting the day and month parts confused

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jan 11, 2016 at 20:58

RiggsFolly

94.7k22 gold badges108 silver badges152 bronze badges

Collectives™ on Stack Overflow

Insert date value in Mysql DB

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related