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?
Convert VARCHAR MySQL field into MySQL dates
Moderator: General Moderators
- 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
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.