CODEWITHSUNDEEP
mysql
Int-khab
Int-khab

Reputation: 153

How to convert and use varchar data type as a datetime in mysql

Hello all, This is the format of my my-sql data type "rdate". Apr 1 2011 01:13:00:000PM

I want to use the order by rdate and i can't make it right order as the data type of rdate is varchar, So i want to convert it to date time , But no success. I am trying to use date_format(str_to_date(rdate, '%m/%d/%Y'), '%Y%m');

Thanks Mypixel

Upvotes: 1

Views: 110

Answers (3)

Filipe Silva
Filipe Silva

Reputation: 21657

Try doing:

ORDER BY str_to_date(rdate,'%M %d %Y %h:%i:%s')

From the docs:

Your Date is in the Following format:

%M  Month name (January..December)
%d  Day of the month, numeric (00..31)
%Y  Year, numeric, four digits
...

You have to tell str_to_date the format that your string is in. This means the way the specific parts of the date are displayed, spaces, etc.

sqlfiddle demo

Upvotes: 3

Mihai
Mihai

Reputation: 26784

UPDATE table SET rdate=str_to_date(rdate,'%M %d %Y %h:%i:%s')

Just convert your column for good to datetime.

Upvotes: 1

BWS
BWS

Reputation: 3836

In your str_to_date function call, you need to specify what the format IS, not what you want it to be. Try this:

str_to_date(rdate, '%M %d %Y %h:%i:%s'));

Upvotes: 2

Related Questions