Convert MS Access Date to Unix Timestamp to MySQL Timestamp

I came across a problem today where I needed to convert a date in an MS Access Database to a MySQL timestamp using PHP.

The solution was pretty easy, but took some thinking through. Here is the answer to save you some time if you need it.

1. Get the date from your MS Access database. If you’re using PHP to do this, you’ll likely use the odbc_exec($connection, $query) function and the odbc_fetch_array() function.
2. Convert the MS Access date format into a UNIX Timestamp using PHP’s strtotime() function.
3. Convert the UNIX Timestamp to MySQL Timestamp using PHP’s date() function and proper formatting.

In all it will look something like this:

$q = "SELECT startdate FROM datetable WHERE ID = 1";

$rs = odbc_exec($conn, $q);

while( $row = odbc_fetch_array($rs) ) {
   $date = strtotime($row['startdate']);
   $date = date('YmdHis',$date);

   //do whatever you want with the date that is now in MySQL Timestamp form
 }

One tricky part is that if you look at a MySQL Timestamp within the MySQL database itself, it will often have a form like ‘2011-06-08 12:03:01’ with dashes and a space. Don’t let this fool you. If you add dashes and a space you’ll get a MySQL error for improper formatting.