从由读取Excel MM / DD / YYYY转换日期发送到数据库之前，YYYY / MM / DD

问题描述

我导入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）;
 

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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！