Sqlite datetime Read and write format issues

I have a problem today.

First insert the data in a datatime format:

String sql= "INSERT into [table] (Date_time) VALUES (' + 

date_time.") ToString () + "'";

Execute as above. Insert does not report an error.

However, when I read it again with a single command:

String Sql= "select * from [table];
" .....
IDataReader Dr=cmd. ExecuteReader ();
...

Object obj=dr["Data_time"];//error here, said is unable to convert to the Datatime format, the string is incorrect

------------------------------------------------------------------- -------------------------

I looked for a day of tutorials and found Google for the day.

The answer is: SQLite Universal Time UTC, to convert several with the DateTime () function.

I also tried, and found that it seems to run not like the tutorial said!

There is no way, to hard, to look at the source code.

His inheritance format is roughly as follows:

Sqliteconvert-->sqlitebase-->sqlite3

The conversion format is defined in Sqliteconvert, which is clearly stated, and the default datatime format is ISO8601

Then, Sqliteconnection used the Sqlite3.

I have studied the source code of Sqliteconnection and found that SQLite is not used in the global UTC time.

In fact, the international standard ISO 8601 is used.

Then I'll start to see how I can make my program compatible with SQLite.

What is the difference between the string I generate and the SQLite.

Finally, I looked at MSDN's description of ISO 8601: (http://msdn.microsoft.com/zh-cn/library/ms187819.aspx)

A string example is given:

* 2004-05-23t14:25:10

* 2004-05-23t14:25:10.487

And I generated the string data_time. ToString () and his have a different place.

is not that t .... (In fact, I also do not understand that T has any key role, anyway access,mysql,mssql do not have this problem)

And I was thinking, what if I used a T?

Just try it. With Data_time. ToString ("s"); This method is converted to ISO 8601 standard string format

As a result, I ate a piece of sauce board head (Wuxi dialect, meaning good luck), unexpectedly succeeded.

As follows:

String sql= "INSERT into [table] (date_time)" VALUES (' + 

date_time. ToString ("s") + "'";

In this way, the problem is solved, I hope you can see this paste, and carry forward, spread everywhere, so as to develop sqlite penetration rate.

Note:

I first studied Bbsmax's SQLite database. I think this forum will have a good solution.

But I was wrong. Actually, no. Bbsmax used varchar instead of datetime format.

I did, too, but when I used the select * from [table] where date_time > ' 2006-1-1 ' and date_time< ' 2008-1-1 '

A statement like this, when you want to query a middle time type, syntax error.

So, in the end, I'm starting to look at the DateTime format from the beginning. String format can be a good, but not so convenient and powerful.