CODEWITHSUNDEEP
mysqlsql
Rathankunu
Rathankunu

Reputation: 45

MySQL date converstion

I have a table in MySQL, where I have a date and time column. Where format of this column is 01 February 2013 12:49:40. I want to extract the date part and time part as well as month part also. I am using month(01 February 2013 12:49:40) this function but it's throwing error. I can't use the Substring, as the width of the row is not equal. What should I do.

Upvotes: 0

Views: 45

Answers (1)

fancyPants
fancyPants

Reputation: 51938

First convert it to a real date (what you have is just text, MySQL doesn't know it's a date)

 SELECT STR_TO_DATE(your_column, '%d %M %Y %H:%i:%s')

then you can use functions like year(), month(), time(), whatever to get what you want...

 SELECT YEAR(STR_TO_DATE(your_column, '%d %M %Y %H:%i:%s')) FROM your_table...

Even better would be of course, to change the column to datatype datetime or timestamp. Then you could spare yourself the STR_TO_DATE() function every time.

Do so by adding a column datetime or timestamp to your table

ALTER TABLE your_table ADD COLUMN your_column datetime;

Then "copy" your column with

UPDATE yourTable SET new_column = STR_TO_DATE(old_column, '%d %M %Y %H:%i:%s');

Then you can drop your current column.

ALTER TABLE DROP COLUMN your_current_column;

Or choose another approach, several possibilities...

Upvotes: 3

Related Questions