How do I convert a date of the format DDMMMYYYY into a db storable date

Question

Hi guys my application parses text files where date values are stored in the format: DDMMMYYYY eg: 12MAR2009.

I noticed that inserting this value as it is into a database datetime value doesn't work at all. How do I convert this into a datetime value which can be entered into a database. My application is in php.

Answer 1

$timestamp = strtotime("12MAR2009");

...

INSERT INTO my_table (..., timestamp) VALUES (..., FROM_UNIXTIME($timestamp))

The PHP function strtotime() will take almost any string containing a date/time value, and convert it to a unix timestamp.

The MySQL function FROM_UNIXTIME() will take a unix timestamp and convert it to MySQL's datetime format.

Answer 2

Something like this should convert your date into MySQL's DATETIME format:

$date = new DateTime('12MAR2009', new DateTimeZone('America/New_York'));
$mysql_date = $date->format('Y-m-d H:i:s');

You can use strtotime(), but timezone handling with it is kind of a pain.