PHP Versions Question posted in
You can find a list of all versions here.

Php versions – How to convert VARCHAR date to a standard MYSQL date format when doing LOAD DATA INFILE

In my table I have an entire column that has varchar date, here is what it looks like: 2/1/2020 02:30:00 (date is m/d/yyyy) I want it to convert to standard MySQL date format (yyyy-mm-dd) or maybe if possible a customized date format version . I also wanted to remove the time (02:30:00) if possible. Is there any command that I can run on terminal to convert the entire column or an in import to database, from .xls to .sql file format. Another thing in case it is not possible in MySQL, maybe there is a way in PHP to convert or manipulate the said data. Thank You.

Additional, maybe there is a way to convert the time too, to the standard MySQL time format.

I am not sure how else to change my question, here are some of additional data.

enter image description here

enter image description here
enter image description here

enter image description here

that is the result of the show warnings, Im not sure were is this going, and what I am doing wrong, but in the given example it is working fine. I will just keep on trying, but I am satisfied that I get two very good suggestion and approach, one on mysql side at php side. Thank you.

Tags:

3

Answers


  1. 0

    Use strtotime and date function for manipulating this date to MySQL Date Format:

    $db_date = "2/1/2020  02:30:00";
$new_date = date( "Y-m-d" , strtotime($db_date) );

    Explanation :

    strtotime($db_date) this will convert string date to unix timestamp

    date( "Y-m-d" , .... ) this will convert unix timestamp to YYYY-MM-DD (Y-m-d) format

    Demo : https://3v4l.org/GOr34

    Login or Signup to reply.
  2. 0

    You can do this translation directly in MySQL, using STR_TO_DATE to translate the string into a DATETIME value, and then DATE and TIME to extract the date and time parts:

    SELECT DATE(STR_TO_DATE('2/1/2020 02:30:00', '%c/%e/%Y')) AS date,
       TIME(STR_TO_DATE('2/1/2020 02:30:00', '%c/%e/%Y %H:%i:%s')) AS time

    Output:

    date        time
2020-02-01  02:30:00

    Demo on dbfiddle

    Login or Signup to reply.
  3. 0 
    LOAD DATA INFILE ...
    (col1, col2, @str)
    SET col3 = STR_TO_DATE(@str, '%c/%e/%Y %H:%i:%s');

    That is, store the incoming string into an @-variable, then convert it within the LOAD DATA statement.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search