CODEWITHSUNDEEP
phpmysqlsql
Aneeez
Aneeez

Reputation: 1452

Convert column of text to time

I have a table named 'slots' with a column whose values are time slots (like 8:00 am, 9:00 am, 9:30 am and so on). But these values are stored as text. I want to convert them to time. I tried the following query

UPDATE 'slots' SET 'slot' = STR_TO_TIME('slot', '%h:%i %p')

But it gave the following error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''slots' SET 'slot' = STR_TO_TIME('slot', '%h:%i %p')' at line 1

Upvotes: 2

Views: 91

Answers (1)

John Ruddell
John Ruddell

Reputation: 25842

you need to use back ticks

UPDATE `slots` SET `slot` = TIME_FORMAT(TIME( STR_TO_DATE( `slot`, '%h:%i %p' ) ), '%h:%i %p' );

right now its being used as a literal string instead of a column. back ticks denote a column or tablename not quote marks

Upvotes: 1

Related Questions