Repair Time Field

[PCHomepage]

I have a field that was formerly a VARCHAR with MM:SS only and it was converted to a TIME field. The conversion, though, seems to have made it HH:MM:00. How can I fix the problem so that it's 00:MM:SS instead? Thanks in advance.

 

[feherke]

Hi

If you still have the original [tt]varchar[/tt] field, is easy :

[b]update[/b] yourtable [b]set[/b] timefield[teal]=[/teal]str_to_date[teal]([/teal]varcharfield[teal],[/teal][green][i]'%i:%s'[/i][/green][teal]);[/teal]

If the original field is gone, I am afraid there is almost nothing to do. As the hour interval ( 0..23 ) is significantly smaller than the minute interval ( 0..59 ), most of the values probably already became [tt]null[/tt]. Even more, I see no way to differentiate between 12am and 12pm. You could try to convert them back and then apply the previous conversion :

[b]update[/b] yourtable [b]set[/b] timefield[teal]=[/teal]str_to_date[teal]([/teal]time_format[teal]([/teal]timefield[teal],[/teal][green][i]'%h:%i'[/i][/green][teal]),[/teal][green][i]'%i:%s'[/i][/green][teal]);[/teal]

Also take a look at how '%h' vs. '%H' differs. Of course, all these after creating a backup.

Anyway, could you tell us exactly what formula you used for the wrong conversion ?


Feherke.

http://free.rootshell.be/~feherke/

 

[PCHomepage]

Thanks! I knew it had to be simple but I couldn't recall the syntax to do it, and it worked perfectly. The data is still there and it should have been MM:SS only so there were no problems with hours, especially since all the minutes were less than 10.

 

[PCHomepage]

To answer your question, I did no "wrong" conversion as such. I simply changed the field type from VARCHAR to TIME to see what would happen. I wanted to SUM the values of certain selections and it seemed much easier if they were true time rather than a varchar pretending to be time. Since the original varchar had only MM:SS, the conversion made it HH:MM:OO, which is what I expected would happen. Thanks again for helping me sort it out.