Notes for reading and writing formats of the SQLite datetime type

This article Reprinted from http://www.tntserver.cn/article.asp? Id = 41

 

I encountered a problem today.
First, insert a data in datatime format:
String SQL = "insert into [Table] (date_time) values ('" +Date_time.tostring ()+ "'";
Execute the preceding command. No error is reported during insertion.

However, when I use another command to read:
String SQL = "select * from [Table];
.....
Idatareader DR = cmd. executereader ();
...
Object OBJ = Dr ["data_time"]; // an error occurred here, indicating that the string cannot be converted to the datatime format. The string is incorrect.

Bytes --------------------------------------------------------------------------------------------

I found a one-day tutorial and a one-day Google.
The answer is: The Global time UTC used by SQLite, which must be converted using the datetime () function.
I also tried it and found that it was not running as described in the tutorial!

There's no way to do it. Let's just look at it.Source codeRight.
The inheritance format is roughly as follows:
Sqliteconvert --> sqlitebase --> sqlite3
The conversion format is defined in sqliteconvert. As mentioned above, the default datatime format is iso8601.
Then, sqliteconnection uses sqlite3.

I studied the sqliteconnection source.CodeIt is found that SQLite is not the global UTC time used.
It is actually the International Standard ISO 8601.

Then I started to see how to make myProgramCompatible with SQLite.
What is the difference between the string I generate and SQLite.

Finally I read the description of ISO 8601 on msdn: (http://msdn.microsoft.com/zh-cn/library/ms187819.aspx)
A string example is provided:
* 2004-05-23t14: 25: 10
* 2004-05-23t14: 25: 10.487
The generated string data_time.tostring () is different from its.
There is no t... (in fact, I don't understand the key role of T. Access, MySQL, and MSSQL are all correct)

I was wondering what would happen if I used a t?
Try. Use data_time.tostring ("S"); this method to convert to ISO 8601 standard string format

As a result, I have eaten a sauce board, which means I'm lucky.

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 post and spread it everywhere to develop the penetration rate of SQLite.

Note:
I first studied the bbsmax SQLite database. I think this forum will provide a good solution.
But I am wrong. Actually no. bbsmax uses varchar instead of datetime format.
I did the same, but when I used select * from [Table] Where date_time> '2017-1-1 'and date_time <'2017-1-1'
A syntax error occurs when you want to query an intermediate time type.

So, in the end, I started to study the datetime format from the beginning. Although the string format can be integrated, it is not so convenient and powerful.