I need to import text files from various sources that will contain dates in various formats. Given how many messed up pieces of data I have encountered in the test files I have gathered so far, I have decided to force the user to enter their date format before importing the file, e.g. MMDDYYYY or DD.MM.YYYY etc.
I can't expect these users to understand enough to enter Y-m-d or n/j/Y formats which would be easy enough to parse, and I can't rely on "standards" like "if a dash is used, then month comes before day", etc., so automatic parsing is very much out of the question.
The other issue with the usual PHP date formatting is that I can't predict if any date format will have single or double digits, and I don't know how createFromFormat would parse this if I give them a single digit when it expects a double digit...
So my first thought is to start by separating pure numeric dates from others and force the user to enter a full 8 digit date along with the format code - otherwise it becomes messy. But that's as far as I've gotten so far...
I thought about parsing date and format string side by side, but that again would cause issues with one-digit numbers if I'm not careful.
I haven't found anything that'll help in my case, but maybe I'm taking this from the wrong angle - maybe there's a Javascript or jQuery date format selection tool out there that's compatible with the PHP date formats...
Any suggestions on the best approach for this?
jquery ui has a good datepicker, i have used it in several apps to ensure the format of dates is always the same
I think I found the simplest solution. I was thinking way too complicated.
I'll restrict the date format input to 2 digits of M, 2 digits of D, and 4 digits of Y with any or no separator, then I can use this:
$str = '02-01-2013';
$format = "DD-MM-YYYY";
$matrix = array(
"YYYY"=>"Y",
"MM"=>"m",
"DD"=>"d"
);
foreach ($matrix as $key=>$value)
{
$format = str_replace($key, $value, $format);
}
$date = Datetime::createFromFormat($format, $str);
echo $date->format('Y-m-d');
This should work for me. Thanks for your suggestions! If someone has a better idea, I'm still open :-)
Instead of letting the user enter a date format, let them choose a predefined one from a drop down menu. That way you will avoid a completely insane person entering "month day-YYYY" as a format or something else odd, or even just making a spelling mistake. Once you have a list of formats you can use php if statements to convert the date into whatever finished format you want.
$dateFormat = $_POST['dateFormat'];
$date = $dateFromFile;
if ($dateFormat == "YYYYMMDD"){
$cleanDate = DateTime::createFromFormat('Ymd', $date);
echo $cleanDate->format('Y-m-d');
}
else if ($dateFormat == "YYYY/MM/DD"){
$explodedDate = explode("/", $date);
$year = $explodedDate[0];
$month = $explodedDate[1];
$day = $explodedDate[2];
$cleanDate = "$year-$month-$day";
echo $cleanDate;
}
else if ($dateFormat == "Month DD, YYYY"){
$explodedDate = explode(" ", $date);
$month = $explodedDate[0];
//change month from word into corresponding number
if ($month == "January" || $month == "january" || $month == "Jan" || $month == "jan" || $month == "Jan." || $month == "jan."){
$cleanMonth = "01";
}
else if ($month == "February" || $month == "february" || $month == "Feb" || $month == "feb" || $month == "Feb." || $month == "feb."){
$cleanMonth = "02";
}
else if ($month == "March" || $month == "march" || $month == "Mar" || $month == "mar" || $month == "Mar." || $month == "mar."){
$cleanMonth = "03";
}
else if ($month == "April" || $month == "april" || $month == "Apr" || $month == "apr" || $month == "Apr." || $month == "apr."){
$cleanMonth = "04";
}
else if ($month == "May" || $month == "may" || $month == "May" || $month == "may" || $month == "May." || $month == "may."){
$cleanMonth = "05";
}
else if ($month == "June" || $month == "june" || $month == "Jun" || $month == "jun" || $month == "Jun." || $month == "jun."){
$cleanMonth = "06";
}
else if ($month == "July" || $month == "july" || $month == "Jul" || $month == "jul" || $month == "Jul." || $month == "jul."){
$cleanMonth = "07";
}
else if ($month == "August" || $month == "august" || $month == "Aug" || $month == "aug" || $month == "Aug." || $month == "aug."){
$cleanMonth = "08";
}
else if ($month == "September" || $month == "september" || $month == "Sep" || $month == "sep" || $month == "Sep." || $month == "sep."){
$cleanMonth = "09";
}
else if ($month == "October" || $month == "october" || $month == "Oct" || $month == "oct" || $month == "Oct." || $month == "oct."){
$cleanMonth = "10";
}
else if ($month == "November" || $month == "november" || $month == "Nov" || $month == "nov" || $month == "Nov." || $month == "nov."){
$cleanMonth = "11";
}
else if ($month == "December" || $month == "december" || $month == "Dec" || $month == "dec" || $month == "Dec." || $month == "dec."){
$cleanMonth = "12";
}
$day = $explodedDate[1];
$year = $explodedDate[2];
$cleanDay = preg_replace("/[^0-9]/","",$day);//remove extra characters from day
$cleanDate = "$year-$cleanMonth-$cleanDay";
echo $cleanDate;
}
You will have to figure out a way to convert each different date format you can think of. So it will take a bit of playing around. You can also give the user option to inform webmaster of an unlisted date format. Then get that option to send you an email so you can add it to the list.