Link to home
Start Free TrialLog in
Avatar of peter-cooper
peter-cooper

asked on

Convert complicated date to yyyy-mm-dd format

Hello
I have a date that is in the form of  
Date {Wed Nov 30 2016 00:00:00 GMT+0000 (GMT Daylight Time)}

Open in new window

. I need to convert this to yyyy-mm-dd for inclusion in MySql. I have looked at various resourves on the internet but nothing seems to work.

I am happy to do conversion in either javascript or php and would be grateful all assistance. Many thanks
Avatar of gr8gonzo
gr8gonzo
Flag of United States of America image

1. Will it always be in English?
2. Is the actual value inside the curly braces { } or is it the full thing including the word "Date" at the beginning?

The date isn't really that complicated. It just has some extra pieces that are preventing automatic parsing. If you can get the string down to a value like this:
Wed Nov 30 2016 00:00:00 GMT+0000

...then most date/time parsing functions will be able to read it just fine. So you might do something like this in PHP:

<?php
// Starting value
$originalValue = 'Date {Wed Nov 30 2016 00:00:00 GMT+0000 (GMT Daylight Time)}';

// Detect curly braces
$posCurlyStart = strpos($originalValue,'{');
$posCurlyEnd = strpos($originalValue,'}');
if(($posCurlyStart !== false) && $posCurlyEnd)
{
  // Get the string inside the { } braces
  $value = substr($originalValue, $posCurlyStart + 1, ($posCurlyEnd - $posCurlyStart - 1));
}
else
{
  // No braces detected
  $value = $originalValue;
}

// Trim off the (GMT Daylight Time)
$posParenStart = strpos($value,'(');
if($posParenStart)
{
  $value = trim(substr($value, 0, $posParenStart));
}

// Now parse it to a DateTime object
$datetime = new DateTime($value);

// Format it to YYYY-MM-DD
echo $datetime->format('Y-m-d'); // 2016-11-30

Open in new window


On a side note, you could also use regular expressions to extract it, but I get the feeling that this kind of date might be repeated a lot, so using strpos and substr should give you faster results than a regular expression (although some longer code).
Avatar of peter-cooper
peter-cooper

ASKER

@gr8gonzo In answer to your questions.

1) Yes.
2) That is how it appeared in firebug after running console.log.

I am using jqwidgets and apparently, there date types use javascript date object. Thanks
FYI var_dump show this in firebug.
["destdate"]=>
  string(53) "Wed Nov 30 2016 00:00:00 GMT 0000 (GMT Daylight Time)"
After running your code, this is what is output from echo: '0000-12-06' which is clearly not correct. Thanks
ASKER CERTIFIED SOLUTION
Avatar of Mukesh Yadav
Mukesh Yadav
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Did you test my EXACT code or did you change it?  If you changed it, then echo out the original value, too. The code is correct but if there's a different format going in, then we need to know that.
Don't use strtotime. The DateTime class is by far better in every regard and also supports date ranges outside of 1970-2038 (which is important for recording DOBs and future expiration dates and things like that). Using strtotime is now a bad habit.
@Mukesh That output '2016-11-30' which is correct. Thanks to all
Thanks once again
Sorry points should have gone Mukesh. How do I change it? Thanks
You're welcome!

I don't have any idea of how to change the accepted solution because I never asked a question here.
Again, I'd strongly recommend that you do not go with the strtotime solution. Or at least just replace the strtotime call with the DateTime class. Read Ray's linked articles for info. Don't set yourself up for failure.
@Ray thanks for that. Interestingly, they make extensive use of 'strtotime' or have I misread the article? Thanks
Part of the confusion here may arise from an inaccurate copy, or from the url-encode and url-decode process.

Consider this:
Date {Wed Nov 30 2016 00:00:00 GMT+0000 (GMT Daylight Time)}

Now compare this:
["destdate"]=> string(53) "Wed Nov 30 2016 00:00:00 GMT 0000 (GMT Daylight Time)"

Note the missing plus sign before the timezone offset.  There is always a plus or a minus sign in this position.  But because of a translation of the plus into a blank, it was lost in the display of "destdate."  This is what caused the '0000-12-06' phenomenon.

FWIW, strtotime() no longer barks about dates outside the 1970-2038 range.  But I agree with the idea that object-oriented date processing is a better way to go most of the time.

You can probably make this whole process easier if you format the date on the JavaScript side of things.
@Ray Unfortunately, jqwidgets docs do not explain how to do this and the forum is far from busy.
Peter, there are two articles.  The procedural article makes use of strtotime()  The object-oriented article uses parallel construction in all of the code samples showing how to do the same things with the DateTime constructor.
Consider this:
Date {Wed Nov 30 2016 00:00:00 GMT+0000 (GMT Daylight Time)}
 This value was console.log from javascript

Now compare this:
["destdate"]=> string(53) "Wed Nov 30 2016 00:00:00 GMT 0000 (GMT Daylight Time)"
This was console.log from php

They bare both correct and only posted what I saw.
I'm not saying you did something wrong - only that the two strings are not equivalent and differ in a way that caused a run-time failure.

Can you please post a link to the jQWidgets docs that describe what you're trying to do, or show us the jQuery statements that generated the date value?  Thanks.
@Ray here is a link to the docs. Thanks

http://www.jqwidgets.com/jquery-widgets-documentation/
Thanks!  Here's an example showing the object-oriented way of converting the JS date() value into the PHP ISO-8601 value.  It uses the ::createFromFormat() method to ignore the unwanted part of the JS date string.
https://iconoun.com/demo/js_date_to_iso_date.php
<?php // demo/js_date_to_iso_date.php
/**
 * Convert a JavaScript date() value to PHP ISO-8601 value
 *  JS Format: Sat May 13 2017 12:20:00 GMT-0400 (Eastern Daylight Time)
 *  ISO-8601:  2017-05-13T12:20:00-04:00
 *
 * Challenge: The "(Eastern Daylight Time)" causes an error in the DateTime constructor
 * Solution: Use DateTime::createFromFormat() with the "+" to skip the trailing data
 *
 * https://www.experts-exchange.com/questions/29022491/Convert-complicated-date-to-yyyy-mm-dd-format.html#a42133534
 * http://php.net/manual/en/datetime.createfromformat.php
 */
error_reporting(E_ALL);


$js_val = !empty($_POST['d']) ? $_POST['d'] : NULL;
if ($js_val)
{
    $format = 'D M j Y H:i:s O+';
    $dt_obj = DateTime::createFromFormat($format,$js_val);
    $dt_iso = $dt_obj->format(Datetime::ATOM);

    echo '<pre>';
    echo PHP_EOL . "JavaScript: <b>$js_val</b>";
    echo PHP_EOL . "DateTime::createFromFormat(<b>$format</b>)";
    echo PHP_EOL . "ISO-8601: <b>$dt_iso</b>";
    exit;
}

// PREPARE OUR FORM USING HEREDOC NOTATION
$form = <<<EOF
<form method="post">
<input name="d" id="client_datetime" type="hidden" />
<input type="submit" value="GET CLIENT DATETIME IN ISO-8601 FORMAT" />
</form>

<script type="text/javascript">
document.getElementById("client_datetime").value = new Date(); // AT PAGE-LOAD TIME
</script>
EOF;

echo $form;

Open in new window