0

I have a Web for that requires the user to enter a date which is then stored in a MySQL database. I'd like to have the user enter the date in m/d/yyyy and have the system convert it into the Y-m-d format that MySQL requires. I thought that was simple enough, but I can't get it to stop making a serious error,

I've tried the following:

$date = new DateTime($this->vital_date);
$this->vital_date = $date->format('Y-m-d');

When the user enter 9/6/2013, 2013-06-09 gets stored in the MySQL table. (Note the transposition of the month and date.

Then, I tried the older, pre-object way:

$date = strtotime($this->vital_date);
$this->vital_date = date('Y-m-d', $date);

And that did the same thing -- a transposed month and date.

Can anyone give me any help on what I'm doing wrong or how I could make a better conversion.

LarryTX
  • 197
  • 2
  • 12
  • 2
    Rather than use a straight DateTime, use the static method [DateTime::createFromFormat()](http://www.php.net/manual/en/datetime.createfromformat.php) method where you specify the format of the date string – Mark Baker Sep 06 '13 at 12:23
  • Maybe all is fine but your mysql is using wrong (European) format? Did you try to save the date and retrieve it again? – cerkiewny Sep 06 '13 at 12:24

4 Answers4

3

You should be using DateTime::createFromFormat

From PHP DOC

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.

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

Example

$vital_date = "9/6/2013";
$date = DateTime::createFromFormat("m/d/Y", $vital_date);
echo $date->format('Y-m-d');
Community
  • 1
  • 1
Baba
  • 94,024
  • 28
  • 166
  • 217
  • You should be using `n` for month and `j` for day. Otherwise it is expecting leading zeros in the format. – Fluffeh Sep 06 '13 at 12:26
  • Can't see any issue in your eval. You need to swap `j` and `n`, so formats will be equal :) – Artem L Sep 06 '13 at 12:36
  • 1
    @Baba i have a strict rule for this: `if (in_array($drink, $alcohol)) exit();` – Artem L Sep 06 '13 at 12:49
  • Actually, I seem to have resolved the issue, and the solution was much simpler than imagined. I was using the JQuery UI DatePicker and failed to notice that it was outputting dates in the format 09-06-2013 rather than 09/06/2013, hence the disambiguation that one you suggested was at the core of the problem. When I corrected that to use slashes instead of dashes, I found that the following worked perfectly: `$this->vital_date = date("Y-m-d", strtotime($this->vital_date));` -- A simple one-liner. Many thanks, you really made me think through all the facets of this one. – LarryTX Sep 06 '13 at 21:48
1

You should use DateTime::createFromFormat for non-standard format

Artem L
  • 10,123
  • 1
  • 20
  • 15
1

You can specify the format you are giving the data in the following way:

$date = DateTime::createFromFormat('j/n/Y', '9/6/2013');
Fluffeh
  • 33,228
  • 16
  • 67
  • 80
-1

This sample is not just obvious for this situation but it is even useful in many other places too for eg getting info from an mathematician entered quadratic equation in your site text box without regex

or driving licence number can be properly handled and stored in a database formatting it using scanf for eg scanf($input, '%s-%s-%d/%d); etc

it depends on your need so you have to know about sscanf, scanf, printf, sprintf, vsprintf

<?php 
$date = '9/6/2013';

    list($month,$day,$year) = sscanf($date, '%d/%d/%d');

    $date = sprintf('%d-%02d-%02d',$year,$month,$day);

echo $date;
?>
gvgvgvijayan
  • 1,851
  • 18
  • 34