Fun with SQL: Part 1

So about 2 years ago, the guy who built the CRM which I work on at work decided that storing dates in the format mm/dd/yyyy as a tinytext was appropriate for one of the tables. So today, after making a bunch of changes to the file that processes this data to format it correctly, I had to change the db data to match the new format. Thus, the magical str_to_date sql statement comes into play. Here it is for future reference:

UPDATE table 
SET timestamp=str_to_date( `timestamp` , '%m/%d/%Y %h:%i:%s' )

This takes the current data (mm/dd/yyyy) and converts it to the proper data type for a DATETIME field (YYYY-MM-DD HH:MM:SS).

Leave a Reply

Your email address will not be published.

Required fields are marked *

You may use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>