MySQL Date Conversion

[dutton]

I have a MySQL database that I need to pull for a client and work with MS Access for a bit of time. Right now, all of the dates in the database tables are being stored as integers appearing like: "1258657399".

Any idea how I can run a sql statement on the database to convert these dates to normal fomrat such as June 9, 2011?

[AnthonySterling]

Sure, in PHP, you can use:-

echo date('F j, Y', $int); 

[dutton]

Thanks but I am hoping to run a sql statement right from within phpmyadmin to convert all of the fields. Is that possible? I am able to change the structure of the table field to varchar from int.

[AnthonySterling]

You should be changing it to a DATETIME field though, otherwise you'd be in a worse mess than you are now.

[AnthonySterling]

I'm sure there's a standard way of doing this, but I'd create another table and select everything from this table (converting the columns as you go) and insert it into the new table.

Check out INSERT INTO ... SELECT ...

[dutton]

Thanks, yea I figured out how to do it in a round about way, same idea. Export table to CSV, open in Excel and create new column using formula to convert the field. Works fine since I need to massage some of the data anyways before brining it into Access.

Thanks!

[AnthonySterling]

Great news.

Please tell me you're now using a DATETIME datatype though, you'll be glad you did later down the line; trust me.

[dutton]

Yep! All is working fine, thanks!

[r937]

Any idea how I can run a sql statement on the database to convert these dates to normal fomrat such as June 9, 2011?

sorry i missed all the excitement

to answer your original question, yes, just do this --

SELECT FROM_UNIXTIME(thesedates,'%M %e, %Y') ...

simple, eh?