convert date string to mysql datetime field

Question

I have a bunch of records with dates formatted as a string such as  04 17 2009   I want to convert them to a mysql datetime field  I plan to use a foreach loop to read the old date value and insert the newly formatted value into a new field in each record  what would be the best way to convert that string   I thought php might have a way to do it automatically   thanks

User · Answer

Use DateTime  createFromFormat like this     date   DateTime  createFromFormat  m d Y H i s    input string   00 00 00     mysql date string    date- gt format  Y-m-d H i s      You can adapt this to any input format  whereas strtotime   will assume you re using the US date format if you use    even if you re not   The added 00 00 00 is because createFromFormat will use the current date to fill missing data  ie   it will take the current hour min sec and not 00 00 00 if you don t precise it

User · Answer

SELECT   FROM table name WHERE CONCAT  SUBSTRING json date  11  4       -   SUBSTRING  json date  7  2       -   SUBSTRING  json date  3  2      gt   NOW      json date   05 11 2011

User · Answer

time   strtotime  oldtime     Then use date   to put it into the correct format

User · Answer

I assume we are talking about doing this in Bash   I like to use sed to load the date values into an array so I can break down each field and do whatever I want with it  The following example assumes and input format of mm dd yyyy     DATE  2 DATE ARRAY   echo  DATE   sed -e  s      g    MONTH   echo   DATE ARRAY 0     DAY   echo   DATE ARRAY 1     YEAR   echo   DATE ARRAY 2     LOAD DATE  YEAR MONTH DAY   you also may want to read up on the date command in linux  It can be very useful  http   unixhelp ed ac uk CGI man-cgi date   Hope that helps        -Ryan

User · Answer

First  convert the string into a timestamp    timestamp   strtotime  string     Then do a  date  Y-m-d H i s    timestamp

User · Answer

If these strings are currently in the db  you can skip php by using mysql s STR TO DATE   function   I assume the strings use a format like month day year where month and day are always 2 digits  and year is 4 digits   UPDATE some table    SET new column   STR TO DATE old column    m  d  Y     You can support other date formats by using other format specifiers

[php] convert date string to mysql datetime field

Examples related to php

Examples related to mysql