从由读取Excel MM / DD / YYYY转换日期发送到数据库之前，YYYY / MM / DD [英] Converting date from mm/dd/yyyy which is read from excel to yyyy/mm/dd before sending it to database

问题描述

我导入Excel表到SQL Server数据库。我可以上传数据，但没有在Excel中列其中包括在格式的日期 MM / DD / YYYY 。我想这个转换为 YYYY / MM / DD 发送到数据库之前：

 的DataTable DT7 =新的DataTable（）;
dt7.Load（DR）;
的DataRow [] = ExcelRows新的DataRow [dt7.Rows.Count]//批量复制到SQL Server
使用（SqlBulkCopy的bulkCopy =新SqlBulkCopy的（sqlConnectionString））
{
  bulkCopy.DestinationTableName =ExcelTable;
  dt7.Rows.CopyTo（ExcelRows，0）;  的for（int i = 0; I＆LT; ExcelRows.Length;我++）
  {
    日期时间oldDate = Convert.ToDateTime（ExcelRows [I] [数据]）日期。
    日期时间newDate = Convert.ToDateTime（oldDate）.Date;
    ExcelRows [I] [数据] = newDate.ToString（YYYY / MM / DD）;
  }
  bulkCopy.WriteToServer（ExcelRows）;
 

  

在错误：日期时间oldDate = Convert.ToDateTime（ExcelRows [I] [数据]）日期;结果
  错误：对象不能从DBNull的转换为其他类型

在我看来，这是阅读错误的列或东西，因为它说不能从DBNull的投。 Excel工作表有两列：

  ID |数据（即日期）
 

我曾尝试更换 ExcelRows [I] [数据] 与 ExcelRows [I] [1] 但我得到了同样的错误。

解决方案

这是因为ExcelRows [I] [数据]返回DBNull。

这种情况发生时，你应该处理它

 如果（ExcelRows [I] [数据] == DBNull.Value）
    {
        //包含任何要执行的操作，如果没有日期
    }
    其他
    {
        日期时间oldDate = Convert.ToDateTime（ExcelRows [I] [数据]）日期。
        日期时间newDate = Convert.ToDateTime（oldDate）.Date;
        ExcelRows [I] [数据] = newDate.ToString（YYYY / MM / DD）;
    }
 

I am importing an Excel sheet into a SQL Server database. I am able to upload the data but there is a column in Excel which consists of dates in format mm/dd/yyyy. I want this to be converted to yyyy/mm/dd before sending it to database :

DataTable dt7 = new DataTable();
dt7.Load(dr);
DataRow[] ExcelRows = new DataRow[dt7.Rows.Count];

// Bulk Copy to SQL Server
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
{
  bulkCopy.DestinationTableName = "ExcelTable";
  dt7.Rows.CopyTo(ExcelRows, 0);

  for (int i = 0; i < ExcelRows.Length; i++)
  {
    DateTime oldDate =Convert.ToDateTime(ExcelRows[i]["data"]).Date;
    DateTime newDate = Convert.ToDateTime(oldDate).Date;
    ExcelRows[i]["data"] = newDate.ToString("yyyy/MM/dd");
  }
  bulkCopy.WriteToServer(ExcelRows);

Error at: DateTime oldDate = Convert.ToDateTime(ExcelRows[i]["data"]).Date;
Error: Object cannot be cast from DBNull to other types.

It seems to me that it is reading wrong column or something since it says cannot be cast from DBnull. The Excel sheet has two columns:

id | data (which is date)

I have tried replacing ExcelRows[i]["data"] with ExcelRows[i][1] but I got the same error.

解决方案

This happens because ExcelRows[i]["data"] returns DBNull.

You should handle it when that happens

    if (ExcelRows[i]["data"] == DBNull.Value)
    {
        // Include any actions to perform if there is no date
    }
    else
    {
        DateTime oldDate = Convert.ToDateTime(ExcelRows[i]["data"]).Date;
        DateTime newDate = Convert.ToDateTime(oldDate).Date;
        ExcelRows[i]["data"] = newDate.ToString("yyyy/MM/dd");
    }

这篇关于从由读取Excel MM / DD / YYYY转换日期发送到数据库之前，YYYY / MM / DD的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！