从SQLite数据库获取日期值 [英] Get date value from SQLite Database
问题描述
我放弃了。
我正在尝试从SQLite数据库中读取日期值。该列定义为DATETIME类型。我成功地将数据写入数据库,但在尝试读取时会抛出错误:
I give up.
I am trying to read a date value from a SQLite database. The column is defined as a DATETIME type. I sucessfully write the data to the database but when trying to read it throws the error: "
String was not recognized as a valid DateTime.
我的代码如下所示:
" My code looks like this:
SQLiteConnection conn = new SQLiteConnection(this.ConnString);
conn.Open();
SQLiteCommand cmd = new SQLiteCommand(conn);
cmd.CommandText = "SELECT * FROM tblAutomatedSearches";
SQLiteDataReader reader = cmd.ExecuteReader();
this.DBID = Convert.ToInt32(reader["ID"]);
this.NotifyEmail = reader["notifyEmail"].ToString();
this.SearchTitle = reader["jobTitle"].ToString();
Object tempObject = reader["endDate"]; // <- throws the error
string tempString = Convert.ToString(reader["endDate"]); //<- will throw the error also if above commented out
日期的格式为2012/06/11 12:51:14。
我知道SQLite引擎实际上将日期值存储为字符串。所以看起来我写的格式不能被阅读。我已经尝试了我能想到的每种格式,例如MM / dd / yy,MM / dd / yyyy,M / d / yy等。
我正在使用参数化查询将数据写入数据库,ala ':
The date is in the format of "2012/06/11 12:51:14".
I understand that the SQLite engine actually stores the date value as a string. So it seems like the format that I write it in cant be read. I have tried EVERY format I can think of, e.g. MM/dd/yy, MM/dd/yyyy, M/d/yy, etc.
I am writing the date to the DB using a paramerterized query, ala':
SQLiteConnection conn = new SQLiteConnection(this.ConnString);
conn.Open();
SQLiteCommand cmd = new SQLiteCommand(conn);
cmd.CommandText = "INSERT INTO tblAutomatedSearches (jobTitle, endDate, notifyEmail, caseNumber) VALUES(@jobTitle,@endDate,@notifyEmail, @caseNumber)";
cmd.Parameters.AddWithValue("@jobTitle", this.SearchTitle);
cmd.Parameters.AddWithValue("@endDate", String.Format("{0:yyyy/MM/dd HH:mm:ss}", this.EndDate)); // <- this.EndDate is a DateTime type
cmd.Parameters.AddWithValue("@notifyEmail", this.NotifyEmail);
cmd.Parameters.AddWithValue("@caseNumber", this.CaseNumber);
cmd.ExecuteNonQuery();
这些插入运行得很好,所以我不得不认为数据库是只是以字符串形式输入日期,但尝试将其作为检索时的日期读取。我肯定错过了什么。我需要能够根据这些条目的日期进行排序,所以我不能只将它们作为文本输入。
非常感谢您的帮助。
These inserts run just fine, so I'm left to think that the database is just entering the date as a string, but trying to read it as a date when retrieving it. I must be missing something. I will need to be able to sort based upon the date of these entries, so I can't just enter them as text.
You help is greatly appreciated.
推荐答案
为什么不让日期/时间成为DateTime类?
Why not just let date/time be a DateTime class?
DateTime endDate = (DateTime)reader["endDate"];
在字符串之间转换日期/时间是没有意义的。只有当你必须为最终用户显示时才将日期/时间转换为字符串。
Converting a Date/Time to and from a string makes no sense. Only convert a Date/Time to a string, when you have to display it for the end user.
DateTime endDate = Convert.ToDateTime(reader [endDate]。ToString()) ;
或
string tempString = Convert.ToDateTime(reader [endDate]。ToString( )。ToString();
DateTime endDate = Convert.ToDateTime(reader["endDate"].ToString());
or
string tempString = Convert.ToDateTime(reader["endDate"].ToString()).ToString();
Error "String was not recognized as a valid DateTime."
将您的系统日期时间格式更改为数据库fild日期时间格式。
i希望它可以帮助您....
Change your system date time format to you database fild datetime format.
i hope it help you....
这篇关于从SQLite数据库获取日期值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!