php version of mysql's str_to_date()

Question

I've come across this in my companies code base, and I'd like to change it to something that doesn't connect to mysql just to just a function:

$sql = "select date_format(str_to_date('$date','$format'),'%Y-%m-%d') startDate ";
$result = mysql_query($sql);
...

Our production env is of the unix variety, as is my dev environment, but some people write code on a windows environment, so strptime() isn't an option.

I've seen similar questions floating around SO, but none with an answer that fits my needs. Is there a simple, or common way to extract dates from strings using a variable format?

The reason the date formats can vary so much is because we're parsing file names from vendors, so we have to be able to handle yyyymmdd, mmddyyyy, ddmmyyyy, ddmonyyyy, etc, etc.

Sort of a duplicate of http://stackoverflow.com/questions/2891937/strtotime-doesnt-work-with-dd-mm-yyyy-format — Brad, Mar 23 '11 at 16:40

score 1 · Answer 1 · answered Mar 23 '11 at 14:59

1

There is the PHP function strtotime() PHP Ref which converts most strings which are a time (including things like "next Thursday") into timestamps, but even it can be somewhat confused when dealing with European and American date structures ("dd/mm/yyyy" versus "mm/dd/yyyy").

You can test it out (without writing a test script) at http://www.functions-online.com/strtotime.html

answered Mar 23 '11 at 14:59

Luke Stevenson

10,357
2
26
41

The European/American date structures are exactly why I can't use strtotime(). What date is 12112011, for example. – Parris Varney Mar 23 '11 at 15:11
Then I guess you are stuck with creating your own function to slice the string, analyse it to determine the format (or apply a known format) reform it into a predictable format and then parse it. Also, just to check, you are aware that `strtotime()` will assume European standard if delimited with dashes and American if delimited by slashes? Not sute whether that might help with your problem or not. – Luke Stevenson Mar 23 '11 at 15:27
Oh, and parsing a chunk like "12112011" is not really something which can be solved programmatically (unless there are rules regarding the expected value which you can then test each possible value against). That is really something you need to fix at the input end of things (forcing input to be in a specified format). – Luke Stevenson Mar 23 '11 at 15:28
We've got the format saved in the db, so, I was looking for something that would understand something like date_from_format('12112011', 'mdY'); I didn't know that about the dashes and slashes in strtotime() though. I might be able work with that, thanks. – Parris Varney Mar 23 '11 at 15:43

CrayonViolent · Accepted Answer · 2011-03-23T16:28:25.657

The short answer is that there is no way to automatically parse arbitrary date formats that will correctly parse all formats out there. In your own example formats, there is just no way to know which numbers are for which date increments.

You can look at the accepted formats of strtotime() and if all your vendors use formats recognized by it, then you are in luck. But if not, the best you can really do is create a lookup table of "vendor" => "format" and then you can use date_parse_from_format()

edit: *based on comment below, here is a php4 version of an approximation of date_parse_from_format() that should suit your needs*

function date_parse_from_format($format, $date) {
  $dMask = array('H'=>'hour','i'=>'minute','s'=>'second','y'=>'year','m'=>'month','d'=>'day');
  $format = preg_split('//', $format, -1, PREG_SPLIT_NO_EMPTY);  
  $date = preg_split('//', $date, -1, PREG_SPLIT_NO_EMPTY);  
  foreach ($date as $k => $v) {
    if ($dMask[$format[$k]]) $dt[$dMask[$format[$k]]] .= $v;
  }
  return $dt;
}

Basically you need to have a lookup table of vendor => format where format is a mask of what each character in the date string represents.

NOTE: The masks used for each date/time increments do NOT exactly reflect what is used in php's normal date() string format. It is simplified and meant to mask each individual character of your custom string.

Example:

/*
  lookup table for vendors
  for EVERY character you want to count as a date/time 
  increment you must use the following masks:
  hour   : H
  minute : i
  second : s
  year   : y
  month  : m
  day    : d
*/
$vendorDateFormats = array(
  'vendor1' => 'yyyymmdd',
  'vendor2' => 'mmddyyyy',
  'vendor3' => 'ddmmyyyy',
  'vendor4' => 'yyyy.mm.dd HH:ii:ss'
);

// example 1:
echo "<pre>";
print_r(date_parse_from_format($vendorDateFormats['vendor2'],'03232011'));

// example 2:
echo "<pre>";
print_r(date_parse_from_format($vendorDateFormats['vendor4'],'2011.03.23 12:03:00'));

output:

Array
(
    [month] => 03
    [day] => 23
    [year] => 2011
)

Array
(
    [year] => 2011
    [month] => 03
    [day] => 23
    [hour] => 12
    [minute] => 03
    [second] => 00
)

Unfortunately we're not using 5.3, but date_parse_from_format() is exactly the type of function I was looking for. Thanks. http://us2.php.net/manual/en/datetime.createfromformat.php was also linked form the link you sent me, which would have been helpful if I had a newer php version. — Parris Varney, Mar 23 '11 at 15:13
ah that sucks. Okay see edit above for a php4 version of it that should work for you — CrayonViolent, Mar 23 '11 at 16:28

php version of mysql's str_to_date()

2 Answers2