Converting date to a format MYSQL understands?

Question

I have an variable which holds a date in UK format, ie 250711 which means the 25th July 2011

When I insert that into MYSQL it becomes 2025-07-11

How can I convert it before writing into MySQL such that is displayed correctly in MySQL as 2011-07-25?

Thanks alot,

Greg

but that's what I have to start with, it's come from the output of a solar panel invertor so I cannot change the source.... — kitenski
– kitenski, Commented Jul 28, 2011 at 11:43
@VolkerK got your solution, but If you need to process the date in PHP somewhere else in the code, you can use this strptime("250711", "%d%m%y") — Jacob
– Jacob, Commented Jul 28, 2011 at 11:48

VolkerK · Accepted Answer · 2011-07-28 11:45:49Z

6

If you want to use the string for an insert/update/select statement anyway you can let MySQL do the work

INSERT INTO ... STR_TO_DATE('250711','%d%m%y')

see http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_str-to-date

edited Jul 28, 2011 at 11:45

answered Jul 28, 2011 at 11:45

VolkerK

96.3k20 gold badges169 silver badges232 bronze badges

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

Comments

awsmketchup · Accepted Answer · 2013-07-05 08:15:42Z

0

Hey i hope it ain't toooo late to answer this; however it should help people who will view this question in the future

Just use this function:

function mysqlDate($input) {
  $output = false;
  $d = preg_split('#[-/:. ]#', $input);
  if (is_array($d) && count($d) == 3) {
    if (checkdate($d[1], $d[0], $d[2])) {
      $output = "$d[2]-$d[1]-$d[0]";
    }
  }
  return $output;
}

Lets say that you have a date input as $_POST['date'], the function usage is going to be as in below:

$newDate = mysqlDate($_POST['date']);

Just store the $newDate in your database. Works like magic.

answered Jul 5, 2013 at 8:15

awsmketchup

1222 silver badges14 bronze badges

Collectives™ on Stack Overflow

Converting date to a format MYSQL understands?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related