CODEWITHSUNDEEP
mysql
Jake
Jake

Reputation: 3486

How to convert date in MYSQL to correct format

My current date formats are 01/01/2013 .... DD/MM/YYYY

How can I convert them all into MYSQL dates? I'm under The impression they are in the format YYYY-MM-DD

I don't know where to start.

my problem is that the dates are being ordered in the american way whilst they are in british format :(

Upvotes: 1

Views: 1971

Answers (5)

Luke James
Luke James

Reputation: 131

I have a very similar situation. I converted American date format "%Y-%d-%m" to correct format '%Y-%m-%d'. This is how I did it...

    update table_name set my_date = DATE_FORMAT( STR_TO_DATE( my_date, '%Y-%d-%m' ) , '%Y-%m-%d' );

The first date string format '%Y-%d-%m' needs to be how the date is currently formatted in your table. The second date string is what you want to convert it to.

Upvotes: 0

Amit Garg
Amit Garg

Reputation: 3907

Use the following query.

update tbl_name set your_field_name= str_to_date(your_field_name, '%d/%m/%Y');

It will update the value of your date from DD/MM/YYYY to YYYY/MM/DD.

Then you can change your filed type to date.

Upvotes: 1

jmail
jmail

Reputation: 6134

How to convert date in MYSQL to correct format:

SELECT DATE_FORMAT(column_name, '%d/%m/%Y') FROM tablename 

or

SELECT t.id,  DATEDIFF(STR_TO_DATE(t.carddate, '%m/%d/%Y'), CURDATE)
FROM TABLE t

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_get-format

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

Upvotes: 0

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

Why not DATE_FORMAT

DATE_FORMAT(date,'%Y-%m-%d')

Upvotes: 0

user3470953
user3470953

Reputation: 11315

You can extract each part in php and concat the dd, mm, yyyy and save it to the DB

Upvotes: 0

Related Questions