By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,304 Members | 1,253 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,304 IT Pros & Developers. It's quick & easy.

Convert US date to MySql date

code green
Expert 100+
P: 1,726
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]
Aug 27 '08 #1
Share this Question
Share on Google+
8 Replies


code green
Expert 100+
P: 1,726
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.
Aug 28 '08 #2

Dormilich
Expert Mod 5K+
P: 8,639
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)
Aug 28 '08 #3

code green
Expert 100+
P: 1,726
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
Aug 28 '08 #4

Dormilich
Expert Mod 5K+
P: 8,639
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.
Aug 28 '08 #5

dlite922
Expert 100+
P: 1,584
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
Aug 28 '08 #6

Atli
Expert 5K+
P: 5,058
Couldn't you just use a regex on the CVS file before it is read into your array?
Aug 28 '08 #7

P: 14
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)
Expand|Select|Wrap|Line Numbers
  1. "0375","GF","Brentwood","2008.27.08"
  2. "0376","GF","O St. Market","2008.27.08"
  3. "0378","Gs","Tivoli Square","2008.27.08"
  4. "0383","ods","Van Ness","2008.27.08"
  5. "0379","Foods","Wisconsin Ave - GROCERY","2008.27.08"
  6.  
And this is the mysql query
Expand|Select|Wrap|Line Numbers
  1. LOAD DATA INFILE "C:/Documents and Settings/Dheeraj/Desktop/test.csv" 
  2. INTO TABLE from_csv 
  3. FIELDS TERMINATED BY "," 
  4. OPTIONALLY ENCLOSED BY '"' 
  5. LINES TERMINATED BY '\r\n'
  6. (id, name,city,@varDate)
  7. SET curDate=CONCAT(SUBSTRING_INDEX(@varDate,'.',1),'-',SUBSTRING_INDEX(SUBSTRING_INDEX(@varDate,'.',-1),'.',1),'-',SUBSTRING_INDEX(SUBSTRING_INDEX(@varDate,'.',-2),'.',1));
  8.  
And this is the structure of my table
Expand|Select|Wrap|Line Numbers
  1. id            varchar(50) 
  2. name      varchar(50)
  3. city         varchar(50)  
  4. curDate   date 
  5.  

Hope this will help you.

Best of luck.
Aug 29 '08 #8

code green
Expert 100+
P: 1,726
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
Aug 29 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.