字符串到日期时间转换？ [英] String to datetime conversion?

问题描述

我想通过添加一些列将数据从excel导入数据库。但是当我在datatable中添加日期列时，它会给出错误字符串未被识别为有效的DateTime。



我是什么尝试过：

I want to import data from excel to database with adding some columns. But when i add date column in datatable it gives error "String was not recognized as a valid DateTime."

What I have tried:

try
       {
           string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes'", path);
               connection = new OleDbConnection();
               connection.ConnectionString = excelConnectionString;
        OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
               connection.Open();
               DbDataReader dr = command.ExecuteReader();
       DataTable table = new DataTable("Customers");
       table.Load(dr);
       table.Columns.Add("UploadedFromDate", typeof(DateTime));
       table.Columns.Add("UploadedToDate", typeof(DateTime));
       table.Columns.Add("CreatedDate", typeof(DateTime));

           foreach (DataRow row in table.Rows)
           {

               row["UploadedFromDate"] = DateTime.ParseExact(txtfromdate.Text.Trim(), "yyyy-MM-dd HH:mm tt", null);
               row["UploadedToDate"] = DateTime.ParseExact(txttodate.Text.Trim(), "yyyy-MM-dd HH:mm tt", null);
               row["CreatedDate"] = System.DateTime.Now;
           }

推荐答案

，连接）;
connection.Open（）;
DbDataReader dr = command.ExecuteReader（）;
DataTable table = new DataTable（Customers）;
table.Load（dr）;
table.Columns.Add（UploadedFromDate ，typeof（DateTime））;
table.Columns.Add（UploadedToDate，typeof（DateTime））;
table.Columns.Add（CreatedDate，typeof（DateTime））;

foreach（table.Rows中的DataRow行）
{

row [UploadedFromDate] = DateTime.ParseExact（txtfromdate.Text.Trim（），yyyy-MM -dd HH：mm tt，null）;
row [UploadedToDate] = DateTime.ParseExact（txttodate.Text.Trim（），yyyy-MM-dd HH：mm tt，null）;
row [CreatedDate] = System.DateTime.Now;
}

", connection); connection.Open(); DbDataReader dr = command.ExecuteReader(); DataTable table = new DataTable("Customers"); table.Load(dr); table.Columns.Add("UploadedFromDate", typeof(DateTime)); table.Columns.Add("UploadedToDate", typeof(DateTime)); table.Columns.Add("CreatedDate", typeof(DateTime)); foreach (DataRow row in table.Rows) { row["UploadedFromDate"] = DateTime.ParseExact(txtfromdate.Text.Trim(), "yyyy-MM-dd HH:mm tt", null); row["UploadedToDate"] = DateTime.ParseExact(txttodate.Text.Trim(), "yyyy-MM-dd HH:mm tt", null); row["CreatedDate"] = System.DateTime.Now; }

错误消息不言自明无论是什么在txtfromdate.Text .Trim（）（或出现错误消息的任何行）无法使用yyyy-MM-dd HH：mm tt转换为日期。我们无法看到您的数据，因此我们不知道数据是什么或者原因可能是什么，但如果文本与*完全匹配*则会出错。使用一系列格式使用TryParseExact，或者在无法转换文本时尝试处理，以免引发错误。这一切都取决于你想要的代码流程。

The error message is self-explanatory. Whatever is in "txtfromdate.Text.Trim()" (or whatever line the error message occurs on) can't be converted to a date using "yyyy-MM-dd HH:mm tt". We can't see your data so we don't know what the data is or why that might be, but if the text doesn't match *exactly* it willl error. Either use TryParseExact using a range of formats, or try handling when the text can't be converted so the error isn't raised. It all depends on what you want the flow of your code to be.

这篇关于字符串到日期时间转换？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！