CODEWITHSUNDEEP
mysqldateformat
jQuerybeast
jQuerybeast

Reputation: 14490

Change MYSQL date format

I have a table in MYSQL of which 3 columns have dates and they are formatted in the not desired way.

Currently I have: mm/dd/yyyy and I want to change those dates into dd/mm/yyyy.

Table name is Vehicles and the columns names are:

CRTD
INSR
SERD

Upvotes: 0

Views: 5861

Answers (4)

AnandPhadke
AnandPhadke

Reputation: 13486

try this:

select date_format(curdate(), '%d/%m/%Y');

In you case you have to use this query.If all three columns are of datetime type

select date_format(CRTD, '%d/%m/%Y'),
       date_format(INSR, '%d/%m/%Y'),
       date_format(SERD, '%d/%m/%Y')
From yourTable

Upvotes: 1

John Woo
John Woo

Reputation: 263693

Your current datatype for your column is not date right? you need to convert it to date first using STR_TO_DATE() and convert back to string

SELECT DATE_FORMAT(STR_TO_DATE(colName, '%c/%d/%Y'), '%d/%c/%Y')
FROM table1

SQLFiddle Demo

Upvotes: 4

Ertunç
Ertunç

Reputation: 829

UPDATE Vehicles SET yourdatecolumn=DATE_FORMAT(STR_TO_DATE(yourdatecolumn, '%c-%d-%Y'), '%d-%c-%Y')

Upvotes: 0

Barranka
Barranka

Reputation: 21047

You can use the date_format() function to format your dates any way you want.

If you want to change the format for every date on any database you work with, you should change the value of the date_format system variable.

Upvotes: 0

Related Questions