mysql change date format

Question

I have a date field (tinytext) holding date information in format of "dd-mm-yy" e.g 07-01-90. Using a mysql query I want to change it to yyyy-mm-dd date format. I tried the code below but nothing happens.

mysql_query("UPDATE Table SET date=STR_TO_DATE('date','%Y,%m,%d')");

try with DATE_FORMAT()

xkeshav
– xkeshav

2012-02-24 07:32:47 +00:00
Commented Feb 24, 2012 at 7:32 — xkeshav
– xkeshav, Commented Feb 24, 2012 at 7:32

TazGPL · Accepted Answer · 2012-05-22 09:48:35Z

9

You're using the correct function STR_TO_DATE(str,format) to achieve the goal, but you're making two mistakes:

In your query the format argument does not match the string expression format. You said it's in dd-mm-yy format while you passed %Y,%m,%d (comma separated) to the format argument. The query will return a "incorrect datetime value" error. You should use %d-%m-%Y.
You can't change data type of a column on the fly, by setting different type of the value being passed. You have to first update the values and then change data type for column.

So, summarizing:

mysql_query("UPDATE `Table` SET `date` = STR_TO_DATE(`date`, '%d-%m-%Y')");
mysql_query("ALTER TABLE `Table` CHANGE COLUMN `date` `date` DATE");

Additionally, consider switching to the recommended PDO extension in place of old and slowly deprecated mysql extension.

answered May 22, 2012 at 9:48

TazGPL

3,7442 gold badges41 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Naveen Kumar · Accepted Answer · 2012-02-24 08:33:10Z

5

Error in your query

is STR_TO_DATE(date, '%Y-%m-%d')

mysql_query("UPDATE Table SET date=STR_TO_DATE(date,'%Y-%m-%d')");

answered Feb 24, 2012 at 8:33

Naveen Kumar

4,6011 gold badge20 silver badges39 bronze badges

Comments

devpro · Accepted Answer · 2015-12-29 06:34:22Z

2

Try this:

INSERT INTO table(date_field) VALUES(STR_TO_DATE('December 8, 2010','%M %d,%Y'));

edited Dec 29, 2015 at 6:34

devpro

16.1k3 gold badges29 silver badges39 bronze badges

answered Jul 21, 2014 at 6:41

Karan

5851 gold badge6 silver badges17 bronze badges

Comments

Korhan Ozturk · Accepted Answer · 2012-02-24 07:35:40Z

1

Try it with DATE_FORMAT() function.

 mysql_query("UPDATE Table SET date=DATE_FORMAT(date,'%Y,%m,%d')");

answered Feb 24, 2012 at 7:35

Korhan Ozturk

11.3k6 gold badges38 silver badges50 bronze badges

Comments

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

To display 2 digit year

mysql_query("UPDATE Table SET date=DATE_FORMAT(date,'%y-%m-%d')");
To display 4 digit year

mysql_query("UPDATE Table SET date=DATE_FORMAT(date,'%Y-%m-%d')");

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Feb 24, 2012 at 7:35

Fahim Parkar

31.8k46 gold badges169 silver badges285 bronze badges

2 Comments

mustafa Over a year ago

This query does not convert format... it is probably because the field is "tinytext" not DATE or TIMESTAMP.

Fahim Parkar Over a year ago

you meant variable date is of varchar type??

aksu · Accepted Answer · 2013-11-27 18:16:23Z

1

I'd say you have to do this:

UPDATE table_name SET date = DATE_FORMAT('date', %Y-%m-%d);

edited Nov 27, 2013 at 18:16

aksu

5,2365 gold badges26 silver badges39 bronze badges

answered Feb 24, 2012 at 7:36

helle

11.8k9 gold badges58 silver badges86 bronze badges

Comments

Kumar V · Accepted Answer · 2012-02-24 07:34:19Z

0

If you are using my_sql with php, you can use date function

answered Feb 24, 2012 at 7:34

Kumar V

8,8289 gold badges44 silver badges60 bronze badges

Collectives™ on Stack Overflow

mysql change date format

7 Answers 7

Comments

Comments

Comments

Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

Comments

Comments

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related