[SOLVED] date format, month and day getting switched

[ahs10]

so i have an application that allows you to download and upload excel spreadsheets to interact with the contents of the database. it all works well except i'm running into a problem with the date format. excel stores dates in the n/j/Y format, but it calls this format m/d/yyyy. so my php application must write it's date a date in the n/j/Y format into a cell that's formatted as m/d/yyyy type.

 

$date_format =& $workbook->addformat(array(num_format => 'm/d/yyyy'));

 

$date_sql = $row['date'];

$date_unix = strtotime($date_sql);

$date_excel = date('n/j/Y', $date_unix);

 

$dataWorksheet->write($i, 0, $date_excel, $date_format);

 

so this very condensed excerpt writes the date from the database into a cell of an excel spreadsheet. it works fine, so let's say the date is 7/22/2008. it prints in the cell just fine. now, i want to change that date to 2/8/2008 in the excel spreadsheet, and upload that change back into the database....

 

$date_excel = $data->sheets[0]['cells'][$i][6];

 

$date_unix = strtotime($date_excel);

$date_sql = date('Y-m-d', $date_unix);

 

$query = "UPDATE myTable SET date = '" . $date_sql ."'";

$result = mysql_query($query) or die(mysql_error());

 

the date gets changed in the database, but to 2008-08-02, it reverses the month and day. why? how can i fix this?

[PFMaBiSmAd]

Your problem is in the following -

 

$date_sql = $row['date'];
$date_unix = strtotime($date_sql);

 

$date_sql is in the format yyyy-mm-dd. That is not a standard English time format that strtotime() is able to understand (it thinks this is yyyy-dd-mm.)

 

Use the mysql DATE_FORMAT() function directly in your SELECT query to give you the date in the format you want - http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format

[ahs10]

i love you.  i can't believe i was doing this whole sql to unix to excel thing.  wow, you rock.  thanks!!!

[PFMaBiSmAd]

You will probably like this as well, you can use the mysql STR_TO_DATE() function directly in your UPDATE query - http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_str-to-date and eliminate more php code.