Convert US date to MySql date

[code green]

I am transfering data from a CSV file to a MySql database.
Unfortunately the dates are in 2008.27.08 US string format instead instead of 2008-08-27 database format.
The CSV data is first read into an associative array where the column names become the key names.
So I need to search the keys to find 'date' as part of the key name then convert the element to 'Y-m-d' format.
Is there a way of doing this other than a rather clumsy
[PHP]foreach($record as $key=>&$element)
{
if(strpos($key,'date'))
$element = date('Y-m-d',strtotime($element));
}[/PHP]

[code green]

I have dates in the format 2008.08.12. They are from an oscommerce feed.
How do I convert them to a MySql format.
The following produces 2008-12-08T00:00:00+00:00 [PHP]$date = date('c',strtotime($date)); [/PHP] The month should be August not December.

[Dormilich]

It looks that replacing the "." by "-" should do the trick. I guess the dots make the date() function believe it's an american style date (with day next to year).

Maybe the comments section of the php date() function reference offer some insight (like this one php date comment)

[code green]

Thanks Dormilich.
Replacing the dots with dashes will produce 2008-12-08 which mysql will treat as 8th of December.
The date is 12th of August.
I don't think the format 2008.12.08 as 8th August is recognised anywhere in the world!
Maybe if the server had US settings it could be recognised correctly but my server has UK settings.
Think I will try to persuade the data supplier to mend his ways

[Dormilich]

Think I will try to persuade the data supplier to mend his ways

if that fails you can invert the date manually (like explode - invert - implode or preg_replace/preg_match). I remember seeing one comment (php.net) using something like that.

[dlite922]

FYI:

here's a basic function:

(I didn't write it, copy pasted from an app. )

[PHP]

function convertUSADateToMysql($date)
{
$mysqlDate = "";

if ($date != "")
{
$date = str_replace("/", "-", $date);

list($month, $day, $year) = explode ("-", $date);

if (strlen($day) < 2)
{
$day = "0" . $day;
}

if (strlen($month) < 2)
{
$month = "0" . $month;
}

if (strlen($year) < 3)
{
$year = "20" . $year;
}

$mysqlDate = $year . '-' . $month . '-' . $day;
}
return $mysqlDate;
}

[/PHP]



you're welcome,




Dan

[Atli]

Couldn't you just use a regex on the CVS file before it is read into your array?

[dheeraj857]

If you have to put data from CSV to MySql tables then you can also do in following way.

it is my test csv file.(test.csv)

"0375","GF","Brentwood","2008.27.08"
"0376","GF","O St. Market","2008.27.08"
"0378","Gs","Tivoli Square","2008.27.08"
"0383","ods","Van Ness","2008.27.08"
"0379","Foods","Wisconsin Ave - GROCERY","2008.27.08"
 

And this is the mysql query

LOAD DATA INFILE "C:/Documents and Settings/Dheeraj/Desktop/test.csv" 
INTO TABLE from_csv 
FIELDS TERMINATED BY "," 
OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n'
(id, name,city,@varDate)
SET curDate=CONCAT(SUBSTRING_INDEX(@varDate,'.',1),'-',SUBSTRING_INDEX(SUBSTRING_INDEX(@varDate,'.',-1),'.',1),'-',SUBSTRING_INDEX(SUBSTRING_INDEX(@varDate,'.',-2),'.',1));
 

And this is the structure of my table

id            varchar(50) 
name      varchar(50)
city         varchar(50)  
curDate   date 
 

Hope this will help you.

Best of luck.

[code green]

Wow! Thanks for all the suggestions guys.
I will look at the merits of each one.
The CSV file is opened and read by a class so that needs to remain generic.
And the data is input via a MySql class so I don't really want to add extra load in there.
I need something to manipulate the data in between, but I am hoping to develop this into a generic class eventually so ideally,
I suppose I need a function to handle dates and convert them all to ISO 8601 format (ANSI SQL).from all standards.
Hopefully your ideas will help me write this.

This forum is excellent for bouncing ideas of people helping one to see the true problem more clearly