如何比较数据库中从Excel获取记录的两个日期？ [英] How to compare two dates from database which gets records from Excel?

问题描述

可能重复：

如何将日期从一种格式转换为另一种格式

我有一个包含5列的电子表格。其中之一包含日期列，格式为02年12月12日。

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

当我将Excel工作表提供给数据库时，它存储为空的日期为0000-00-00 00： 00:00。

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

如何将其存储为日期？

这是使用CSV文件向数据库提供数据的代码

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;
                }
        }
        //echo "Found a total of $lines records in this csv file.\n";

推荐答案

只要您使用的是1970年之间的日期仅在2038年， strtotime（）应该可以安全使用。在每个日期列上执行此操作，以将其转换为mySQL的 YYYY-MM-DD 日期格式：

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

确定日期列的最简单方法是指定他们手动。否则，您必须先开始获取表的结构，并查看哪些列是 DATE 或 DATETIME 的列。

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.

这篇关于如何比较数据库中从Excel获取记录的两个日期？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！