'How can I convert String to Date in phpMyAdmin?

I'm a web designer, new to php/sql/phpMyAdmin, but learning fast.

I have imported data with the column "Entry_Date" in the following incorrect format: (mm/dd/yyyy) Which I now need converted to the correct DATE format/type.

I have set up 2 columns
1) Entry_Date - char(10)
2) New_Date - date

In phpMySql, int the myTable's "SQL" tab, I ran;

UPDATE myTable(New_Date)
SELECT STR_TO_DATE(Entry_Date,'%m/%d/%Y') as date FROM `myTable`;

I confirmed the SELECT STR_TO_DATE line is successfully converting the string to DATE, but I am not able to get those values to UPDATE the New_Date column (which is already populatred with "0000-00-00". What am I missing?

phpmysqlsqldatephpmyadmin


Solution 1:[1]

try this

   SELECT STR_TO_DATE(Entry_Date,'%Y-%m-%d')  FROM `myTable`;

or this

  SELECT DATE_FORMAT(Entry_Date,'%Y-%m-%d')  FROM `myTable`;

edit:

  UPDATE myTable SET New_Date = DATE(DATE_FORMAT(Entry_Date,'%Y-%m-%d')) ;

Solution 2:[2]

I am using Linux (Ubuntu 16.04) with MariaDB.

  1. In my situation, the date field data type is set to year. strYear is the datafield that contains the year as a four digit string.

  2. Ran the code below. The code was run from the phpMyAdmin SQL page.

    UPDATE tblBookList SET BookYear = STR_TO_DATE(strYear, '%Y-%m-%d' ) ;

MySQL to SQL Server Data Type Comparisons

Solution 3:[3]

DATE_FORMAT(FROM_UNIXTIME(entry_date), '%d %M,%Y') as new_date

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2
Solution 3 Suraj Rao

Related Questions