格式化日期日期正被从Excel中读取？ [英] Formatting date in date which is being read from excel?

问题描述

我导入excel表到SQL Server数据库表中的excelsheet有：
ID |数据（这是MM / DD / YYYY日期）
我能够将数据上传到数据库表，但我想的日期发送到数据库之前covnerted为YYYY / MM / DD。我尝试了一块code，但没有奏效。

I am importing excel sheet into sql server db table the excelsheet has : id|data(which is date in mm/dd/yyyy) i am able to upload the data into db table but i want the date to be covnerted into yyyy/mm/dd before sending it to database. I tried a piece of code but it did not work

        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++)//i represents the row
            {
                var oldDate = ExcelRows[i]["data"].ToString();
                var newDate = Convert.ToDateTime(oldDate);

                ExcelRows[i]["data"] = newDate.ToString("yyyy/MM/dd");
            }

            bulkCopy.WriteToServer(ExcelRows);

错误：字符串未被识别为有效的DateTime。
我试图在数据库为nvarchar，VARCHAR，日期时间全部3个数据类型。同样的错误

ERROR:String was not recognized as a valid DateTime. i have tried all 3 datatypes in database nvarchar,varchar,datetime. Same error

推荐答案

有关日期列其无法设置不同的格式这里ExcelRows值[I] [数据] = newDate.ToString（YYYY / MM / DD）;如果你想格式化的日期，然后使用多一个字符串列和更新，以你需要保存列，同样的事情。

For date column its not possible to set the value in different format here ExcelRows[i]["data"] = newDate.ToString("yyyy/MM/dd"); if you want formatted date then use one more string column and updated to that column and same thing you need to save.

这篇关于格式化日期日期正被从Excel中读取？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！