Re: Changing a field's data in every record

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 <[email protected]> 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