Link to home
Create AccountLog in
Avatar of bohappa
bohappaFlag for United States of America

asked on

Data in MySQL VarChar column shows A.M. but it wasn't in my original CSV data

I created a CSV file that contains a column of dates:
1/1/2012 8:27

I made the MySQL column a varchar because I couldn't import it properly any other way.

I'm now trying to use a SET statement to grab the varchar data and a STR_TO_DATE function transform the data. Here is the code I want to use:

BEGIN;

 UPDATE `tickets` 
  SET `created_at_converted` = STR_TO_DATE(`created_at`, '%m/%d/%Y %H:%i:%s');
COMMIT;

Open in new window


Of course it fails because my data has AM/PM appended to it:

1/1/2012 8:27:00 AM

I don't know how the AM and PM values were added.

My goal is to have the date data stored as DATETIME or TIMESTAMP. Since I'm only working locally and doing analysis on the ticket data, I don't care about the distinction.
ASKER CERTIFIED SOLUTION
Avatar of Dave Baldwin
Dave Baldwin
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of bohappa

ASKER

Thank you, DaveBaldwin!

I had to change the data type of the column to timestamp and I had to use this:
SET `created_at_datetime` = STR_TO_DATE(`created_at`, '%m/%d/%Y %h:%i:%s %p');

I had to add the seconds parameter.

Thank you!
You're welcome, thanks for the points.