How to compare two dates from database which gets records from Excel? [duplicate]

This question already has answers here: Closed 11 years ago.

Possible Duplicate:

How to convert date from one format to another

I am having a spreadsheet with 5 columns. One of that contain date column with format 02-Dec-10.

When I feed that Excel sheet to database it stores as empty date as 0000-00-00 00:00:00.

How can I store it as date?

Edit

This is the code to feed data to database using CSV file

        $date           =           date("d-m-Y");      
        $databasetable = "sample";
        $fieldseparator = ",";
        $lineseparator = "\n";
        $csvfile = "../uploads/" . basename( $_FILES['file']['name']); 
        $addauto = 0;
        $save = 1;
        $outputfile = "../temp/output.sql";
        if(!file_exists($csvfile)) {
            #echo $csvfile;
            echo "File not found. Make sure you specified the correct path.\n";
            return 0;
            exit;
        }

        $file = fopen($csvfile,"r");

        if(!$file) {
            #echo "Error opening data file.\n";
            return 0;
            exit;
        }

        $size = filesize($csvfile);

        if(!$size) {
            echo "File is empty.\n";
            return 0;
            exit;
        }

        $csvcontent = fread($file,$size);

        fclose($file);

        $lines = 0;
        $queries = "";
        $linearray = array();

        foreach(split($lineseparator,$csvcontent) as $line) {
            $lines++;
            $line = trim($line," \t");
            $line = str_replace("\r","",$line);
            $line = str_replace("'","\'",$line);
            $linearray = explode($fieldseparator,$line);
            $linemysql = implode("','",$linearray);
            if($addauto)
                $query = "insert into $databasetable values('','$linemysql');";
            else
                $query = "insert into $databasetable values('$linemysql');";
            $queries .= $query . "\n";
            @mysql_query($query);
        }

        @mysql_close($con);

        if($save) {
                $file2 = fopen($outputfile,"w");                    
                if(!$file2) {
                    echo "Error writing to the output file.\n";
                    return 0;
                }
                else {
                    fwrite($file2,$queries);
                    fclose($file2);
                    return 1;
                }
        }
开发者_C百科        //echo "Found a total of $lines records in this csv file.\n";

---

As long as you're working with dates between 1970 and 2038 only, strtotime() should be safe to use. Do this on each date column to convert it to mySQL's YYYY-MM-DDdate format:

 $column = date("Y-m-d", strtotime($column)); // Will return 2010-12-02

the easiest way to determine a date column would be to specify them manually. Otherwise, you'd have to start fetching the table's structure beforehand, and looking which columns are DATE or DATETIME ones.

---

Adapted from my post on your other similar question:

For a more general approach, you can always dump your current format to a string, like how you have it, and use string operations to substring and reorganize. I know for a fact that MySQL accepts string values for DATETIME fields.

$day = substr($input, 0, 2);
$month = substr($input, 2, 3);
switch($month){
    case "Jan":
        $month = "01";
        break;
    ...
}

insert this string into your database as its display format