0

I have a database that I receive every month that I simply import through CSV into MYSQL. I don't worry about preserving any old database entries, so I just overwrite the old database with the new entries monthly.

I want to be able to sort through the database by month, however I currently need to import the date field as VARCHAR, or risk having to manually convert tons of entries to fit the formatting of the MYSQL date field.

I would like to take the entries stored in the 'Expiration Date' field and be able to run a query that will convert them to a MYSQL date format and then write them to a new column named 'Expiration_Converted' so that I can then run date functions to show me all dates from a month, year, etc.

I know about the STR_TO_DATE function in MYSQL, however I'm not quite sure how to pull this off. I'm writing everything in PHP.

Any advice would be greatly appreciated.

Thanks!

Stannet
  • 11
  • 2
  • Try editing the question and showing samples of the dates that need to be converted. – Gordon Linoff Dec 30 '14 at 16:38
  • I don't know that the duplicate noted above is actually a duplicate for this question. I suppose it is if you want a PHP solution, but likely there is a simple UPDATE query to be run here to solve this problem. OP should try opening a new question with specific examples of the data format(s) you are trying to change, as well as queries that have been tried that are not working as expected. – Mike Brant Dec 30 '14 at 16:49

1 Answers1

0

Try this one.

I'm still using this script in my web application when I get the value from a text field and need to converto to MySQL DATE Format:

$format = '/^([0-9]{2})\/([0-9]{2})\/([0-9]{4})$/';
    if ($_date != null && preg_match($format, $_date, $parts)) {
        $date = date_create($parts[3].'-'.$parts[2].'-'.$parts[1]);
        $final_formated = date_format($date, "Y-m-d");
        return $final_formated;
    }

PS: $_date variable receives a date string.

After running this script, I can store date in Database.

Hope it helps you.

bcesars
  • 1,016
  • 1
  • 17
  • 36