System.Data.SQLite

Hi Robert,

It would seem that the only reliable way to query a table using a date is to use date.Ticks. If you attempt to use the yyyy-MM-dd you get unreliable results.

When attempting to select data from a table using a query such as - SELECT Column FROM Table WHERE DateColumn = 'yyyy-MM-dd'  the result was always nothing. However, when i changed the query to use SELECT Column FROM Table WHERE DateColumn > 'yyyy-MM-dd'  I would get a result. Upon further inspection i found that SqlLite also return a result for arbitory numbers greater and lower than the date in question. The weird thing was that a query SELECT Column FROM Table WHERE DateColumn > 'yyyy-MM-dd'  AND DateColumn < 'yyyy-MM-dd' returned nothing.

When using date.Ticks eg. SELECT Column FROM Table WHERE DateColumn = date.Ticks the results were as expected. The side effects from this issue include the designers inability to display dates becasue they are Int64. Also, it is not possible to use the designer to author date based queries.

Also, it should be noted that the whole things could be user error on my part.

Technical Details

Windows Mobile 5 Smartphone (IMate SP5)

Compact Framework 2.0

Kind Regards,

Tarek

 

In SQLite, dates are stored by default as strings, using an ISO8601 format.  The default format SQLite.NET uses is "yyyy-MM-dd HH:mm:ss.fffffff".  If you manually insert dates and times as strings and do not use an ISO8601 format, or use inconsistent formatting, you will eliminate your ability to do simple string comparison functions on them, unless you use one of the many datetime functions SQLite has builtin.  I recommend sticking with ISO8601 format if you are able to, as it is the most compatible with other 3rd party tools that use SQLite.

Robert

 

Hi Robert,

If i use the following query "INSERT INTO Table (ID, Name, DateColumn) VALUES (@ID, @Name, @DateColumn)"  and then pass the DateColumn as a SQLiteParameter ie DateColumnParam.Value = Date what will SQLite Store?

What will be the correct syntax for the query?

Can c# output ISO8601 format without manual formatting ie Date.ToString("yyyy-MM-dd HH:mm:ss:ffffff)?

Also, the designer converts dates typed in the following format "yyyy-MM-dd HH:mm:ss.fffffff" to local formats ie 20006-01-15 12:00:00" will be changed to 15/1/2006 (Australia) and then query does not work.

Kind Regards,

Tarek

 

If you set the value of a parameter to a DateTime value, then the ADO.NET wrapper will convert it to a "yyyy-MM-dd HH:mm:ss.fffffff" string in ISO8601 mode when inserting it into the database.  When read back by the ADO.NET provider, the ADO.NET provider will automatically detect that the field is a DateTime, and calling SQLiteDataReader.GetDateTime() will return it as a normal DateTime.

Now in the designer, the schema of the table will dictate that the field is a DateTime field, and the designer will read the value as a DateTime object and may *display* it in your local Australian format -- however, it is stored in the database as a string, in ISO8601 format.  Don't be fooled by the designer's *displaying* of the datetime in your local format -- its stored in the database differently than it is displayed in the designer.

Robert

 

rsimpson:

If you set the value of a parameter to a DateTime value, then the ADO.NET wrapper will convert it to a "yyyy-MM-dd HH:mm:ss.fffffff" string in ISO8601 mode when inserting it into the database.  When read back by the ADO.NET provider, the ADO.NET provider will automatically detect that the field is a DateTime, and calling SQLiteDataReader.GetDateTime() will return it as a normal DateTime.

With SQLite 1.0.40.0, when I want to read a DATETIME from the dataReader, I have to do a .GetString() and then, DateTime.Parse()... else, I get an error...

Hi There! I have a similar question to this.

I am trying to read/convert a datetime field in MySQL to a SQLLIte DateTime format. I have tried various variations without any luck.

 

I tried : 

Console.WriteLine(dr(i).GetDateTime)
Console.WriteLine(CType(dr(i), Date))

'Console.WriteLine(Date.ParseExact(dr(i), "mm/dd/yyyy", System.Globalization.DateTimeFormatInfo.InvariantInfo))
Console.WriteLine(Format$(dr(i), "yyyy-MM-dd hh:mm:ss") & "',")

 

BUT keep on getting an error of >> conversion from type 'MySqlDateTime' to type string is not valid<<

 I am reading a MySQL database from VB.NET and extracting the datetime value and THEN inserting the date into a SQL Lite database.

 Thanks for help in advance..

 

P 

I believe I was having this same problem and I solved my issue by changing my connection string.

 My guess is yours looks like this:

string connectionString = "Data Source = " + FileName + "; Version=3;";

 Try this:

string connectionString = "Data Source = " + FileName + "; Version=3; DateTimeFormat=Ticks;";

 

Maybe someone smarter than me can explain this.

hi, I'm saving datetime data asSystem. DateTime.Ticks and I'm very happy with it. The only thing I would like to have is something like SELECT datetime(1092941466, 'unixepoch'); it would be something like: SELECT TicksToDateTime(AppointmentStart) where AppointmentStart is an ipothetical Table Column defined as integer in SQLITE (System.Int64 in .Net). This way I could build a view to look my appointment table. Is there such a function or is there a way to build it? Best Regards Roberto Dalmonte

Since nobody answered I'll try to answer myself: In theory it should be possible to do it right now using the following syntax ... SELECT datetime((columnAsTicks / 1000000) - 186796800, 'unixepoch') AS Expr1 FROM Table ...unfortunately it doesn't work, at least the way I tried it. The operation is the following: 1) transforms the ticks in seconds (divide ticks per 1 million); 2) subtract the seconds passed from date 0001/01/01 (starting date in System.DateTime) to 1970/1/1 (starting date in Unixepoch) 3) Use the result with the built-in SQLite function datetime. This way you could build a view and be able to see a normal date instead of ticks. Any idea? Roberto

Got it. Here the syntax to make it work select ContactHelper, datetime(StartDate/10000000-62135596800,'unixepoch') as Start, datetime(EndDate/10000000-62135596800,'unixepoch') as End from appointment Thanks to Michael Black (IS) for helping me. please note ContactHelper, StartDate, EndDate are columns of the appointment table. StartDate and EndDate contain System.DateTime saved as ticks. It works now. Roberto Dalmonte