inserting date into mysql

Question

I have a textbox on one of my forms, the user enters a date in the form of dd/mm/yy but when i try to insert it into one of my tables in my database, it enters it as 0000-00-00. how can I fix this? I want it to show on this format on my database dd/mm/yy

this is my following insert where $start is the data variable

  $query = "INSERT INTO paycheck (payCheckId,jobId,payRate,jobLocation,hoursWorked,startPeriod,empId)
  VALUES('','$job_id','$pay_rate','$job_location','$hours','$start','$userId')";
  $result = mysqli_query($db, $query); //we make the query

It will always be store inf the DB as `0000-00-00`. You need to convert all dates to this format before inserting and then convert them to whatever display format you want before displaying them. — prodigitalson, Mar 20 '13 at 00:48
Possible duplicate of http://stackoverflow.com/questions/2167916/convert-one-date-format-into-another-in-php — Oliver Tappin, Mar 20 '13 at 01:11

score 1 · Accepted Answer · answered Mar 20 '13 at 00:52

You can chose either MySQL to do this, or you can have PHP do it for you.

For MySQL related solution, please check STR_TO_DATE() function and for PHP the function date() does it.

Usage in STR_TO_DATE() is:

STR_TO_DATE( '$startPeriod', '%d/%m/%Y' )

Hence, the query will be:

INSERT INTO paycheck (payCheckId, jobId, payRate, 
    jobLocation, hoursWorked, startPeriod, empId)
VALUES( '', '$job_id', '$pay_rate',
    '$job_location', '$hours', 
    STR_TO_DATE( '$startPeriod', '%d/%m/%Y' ), '$userId')

Oliver Tappin · Answer 2 · 2013-03-20T01:10:56.403

You need to reformat your date so MySQL can read it. The format the database saves to is yyyy-mm-dd. If you would like to display the date in the format it originally came in, simply use the date() function like so: $date = date("d/m/y", strtotime($dateFromDB));

This might be a long winded way of doing it, and there are better ways out there however, you can create your own function to reformat it:

function reformatDate($incorrectFormat) {

  list($d, $m, $y) = explode("/", $input);
  $todaysYearBeginning = substr(0, 2, date("Y", time()));


  if(strlen($d == 1) {
    $d = "0" . $d;
  }
  if(strlen($m == 1) {
    $m = "0" . $m;
  }
  if(strlen($y == 1) {
    $y = "0" . $y;
  }

  return $todaysYearBeginning . $y . "-" . $m . "-" . $d;

}

echo reformatDate("dd/mm/yy");

Following on from Dream Eater's answer, you can use DateTime::CreateFromFormat like so:

$oldDate = DateTime::createFromFormat('d/m/y', $dateFromDB);
$newDate = $oldDate->format('Y-m-d');

I'd much rather prefer using [DateTime objects](http://www.php.net/manual/en/datetime.createfromformat.php). — hjpotter92, Mar 20 '13 at 00:57

inserting date into mysql

2 Answers2