从SQLite数据库获取日期值 [英] Get date value from SQLite Database

查看:129
本文介绍了从SQLite数据库获取日期值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我放弃了。



我正在尝试从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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆