php format date pre 1970 fro MySQL insert

Question

I am importing data from a CSV file and one column has a list of dates. Some of these dates are pre 1970. An example date would be 10/03/1956

I've got a problem in formatting these dates into a format that I can insert into MySQL.

if i use this

$date = DateTime::createFromFormat('d/m/Y', $col[8]);

I get this error message

Call to a member function format() on a non-object

but if I hard code a date like this, it works

$date = DateTime::createFromFormat('d/m/Y', '21/03/1966');

here is the complete code

$inputFileName = 'data.csv';

if (($handle = fopen($inputFileName, "r")) !== FALSE) {

    fgetcsv($handle); 

    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {

        $num = count($data);

        for ($i = 0; $i < $num; $i++) {
            $col[$i] = $data[$i];
        }

        try {
            $date = DateTime::createFromFormat('d/m/Y', trim($col[8]));
        } 
        catch (Exception $e) {
            echo $e->getMessage();
            exit(1);
        }

        echo $date->format("Y-m-d");
    }
    fclose($handle);
}

Inspect your CSV and make sure all contents of `$col[8]` are valid dates (which I believe must be after 1901) . — apokryfos, Apr 01 '16 at 13:59
Possible duplicate of [Using strtotime for dates before 1970](http://stackoverflow.com/questions/2871264/using-strtotime-for-dates-before-1970) — Audite Marlow, Apr 01 '16 at 14:01
Are you certain that the string is in correct date format? If you print $col[8] and compare it to $col[7] for example. Are they equal considering the formatting? — Petter Pettersson, Apr 01 '16 at 14:01
I just did a var_dump($col[8]) and it outputted the date as in the CSV — AdRock, Apr 01 '16 at 14:13
My concern is that since you're using `DateTime::createFromFormat('d/m/Y', trim($col[8])` in a loop just 1 of all those entries being invalid (maybe a blank line, maybe an empty entry) would make your code fail. — apokryfos, Apr 01 '16 at 14:15
@apokryfos, I think you could be right, I just checked the CSV and there are some empty dates — AdRock, Apr 01 '16 at 14:17

score 1 · Accepted Answer · answered Apr 01 '16 at 14:48

You have no guarantee that all fields will exist in your CSV file, so you must do a little control

define('DATE_COLUMN', 8);
// @Warning : avoid hard coding file name !
$inputFileName = 'data.csv';

if (($handle = fopen($inputFileName, "r")) !== FALSE) {

    fgetcsv($handle); 

    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {

        $num = count($data);

        for ($i = 0; $i < $num; $i++) {
            $col[$i] = $data[$i];
        }
        $dateString = NULL;
        if (isset($col[DATE_COLUMN]) && 
            strlen(trim($col[DATE_COLUMN])) > 0) {

            $dateString = trim($col[DATE_COLUMN]);
        }

        if (is_null($dateString)) {
            // print log or throw an exception if you want
        }
        else {
           try {
               $date = DateTime::createFromFormat('d/m/Y', dateString);
               echo $date->format("Y-m-d");
           } 
           catch (Exception $e) {
               echo $e->getMessage();
               exit(1);
           }
        }

    }
    fclose($handle);
}

php format date pre 1970 fro MySQL insert

1 Answers1