CODEWITHSUNDEEP
mysqlconnection-string
Jaylen
Jaylen

Reputation: 40311

How to convert Char field to datetime field in mysql?

I have datetime stored in a table as Char(30) and i want to convert them into mysql standred datetime format.

so I tried this

   SELECT STR_TO_DATE(next_attempt, '%Y-%m-%d %T') as date FROM Table LIMIT 10;

This is returning Null always! This is the current format (5/16/2011 20:14) so in my char(30) field i have (5/16/2011 20:14) and I want to convert it to (2011-05-16 20:14:00)

What I doing wrong for it to keep returning Null?

Thanks

Upvotes: 2

Views: 2992

Answers (1)

rcorbellini
rcorbellini

Reputation: 1337

try replace "/" for "," and use '%m,%d,%Y %T'

STR_TO_DATE(replace('5/16/2011 20:14','/',','),'%m,%d,%Y %T')

here works fine:

select STR_TO_DATE(replace('5/16/2011 20:14','/',','),'%m,%d,%Y %T') as date from dual

Upvotes: 4

Related Questions