Erm. I've seen some weird responses to this. Yes, you can do this.
First -- get the data into a usable format. Then, put it into a usable format (eg, timestamp for datetime field). Read up on how mysql interprets date/time data on fields. And, create a new timestamp or date field. Then, do something like this: update table set timestamp_field=concat( SUBSTRING_INDEX(bah,'|',1),"/", SUBSTRING_INDEX(SUBSTRING_INDEX(bah,'|',-5),'|',1),"/", SUBSTRING_INDEX(SUBSTRING_INDEX(bah,'|',-4),'|',1)," ", SUBSTRING_INDEX(SUBSTRING_INDEX(bah,'|',-3),'|',1),":", SUBSTRING_INDEX(SUBSTRING_INDEX(bah,'|',-2),'|',1),":", SUBSTRING_INDEX(SUBSTRING_INDEX(bah,'|',-1),'|',1)); All the data will then be in that timestamp field or datatime column. A datetime column is very readable. As others have mentioned (nicely, and not so nicely), you can easily format the output of a timestamp or datetime as wanted. On Sat, 18 Feb 2017 13:13:38 -0800 debt <d...@fannullone.us> wrote: > I’ve been asked to post a question here for a friend. > > Is there a formula to change the format of the data in a single > field in every record of a table? She has a "timestamp” in a text > field formatted as 2017|02|16|04|58|42 and she wants to convert it to a > more human readable format like 2017-02-16 @ 04:58:42 > > How does one "grab" the existing data and then change it? Can > this be done solely in MySQL, or will she have to grab the data and > then manipulate it in PHP or something? > > Thanks, > Marc > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
