Convert VARCHAR MySQL field into MySQL dates

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
edziffel
Forum Newbie
Posts: 9
Joined: Wed Jul 13, 2011 6:32 am

Convert VARCHAR MySQL field into MySQL dates

Post by edziffel »

Have uploaded a csv source file. One of the data fields that needs to function properly is the date column. The date data in the source file is in the format "dd/mm/yyyy hh:mm or sometimes dd/mm/yyy hh:mm:ss". Don't get me started on what the people who created the data were thinking... lol

To get the data into the database in tack, or rather exactly how it appears in the source file, it was imported as a VARCHAR, with the thinking that from there it could be queried and converted into a data format that could be used to sort by time period reference. Have full control of database, but am not a seasoned pro.

Need an opinion on what the recommended procedure to convert the MySQL text/VARCHAR data into functional date data. Is doing it from a data base even the right approach or does it need to be fixed prior to uploading?
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Convert VARCHAR MySQL field into MySQL dates

Post by AbraCadaver »

It depends on how you are "uploading" and how often you do this. For the data that exists in the DB you can run an update to change the date using STR_TO_DATE() in the query. Then you can change the column type to DATE, or you could create another column for the new date and after update delete it.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Post Reply