mysql change date format

mysql change date format

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')");  

Answer by kumar_v for mysql change date format

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

Answer by Fahim Parkar for mysql change date format

1. To display 2 digit year

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

2. To display 4 digit year

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

Answer by Korhan Ozturk for mysql change date format

Try it with DATE_FORMAT() function.

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

Answer by helle for mysql change date format

I'd say you have to do this:

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

Answer by Naveen Kumar for mysql change date format

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')");  

Answer by bostaf for mysql change date format

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

1. 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.
2. 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.

Answer by Karan Rajput for mysql change date format

Try this:

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

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72