[Solved] Weird problem with date field in SQLite lazarus / 16-Databases ---------------------------------------------------------------------- JanRoza · 2016-01-10 18:42 I have a SQLite table that has a DATE field and a form that has a datetimepicker on it. In the formactivate procedure of the form I initialize the datetimepicker with the date from the SQLite table as follows: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i edt_Datum.Date := DataModule1.qryOnderhoud.FieldByName('Datum').AsDateTime;      The save button on the form updates this database field as follows: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i        DataModule1.qryOnderhoud.Edit;       DataModule1.qryOnderhoud.FieldByName('Datum').AsDateTime := edt_Datum.Date;       DataModule1.qryOnderhoud.Post;       DataModule1.qryOnderhoud.ApplyUpdates;       DataModule1.TransactionDb.CommitRetaining;  Using SQLiteExpert database manager I see the date is correctly updated in the record. Now the weird part: when I sort the database on this DATE field (ascending) this updated record always becomes the first record which is strange because, as you can see from the screenshot, it is definitely not the lowest date. (or the last record when sorting descending) So it looks like the content of the DATE field looks okay but isn't according to the sorting. What is going wrong? Is this a bug in the datepicker component? What am I doing wrong? ---------------------------------------------------------------------- mirce.vladimirov · 2016-01-10 19:39 You could post a picture of the result of your query so we have a more complete view at the problem. How is your date stored, day-month-year or year-month-date or something else ? ---------------------------------------------------------------------- balazsszekely · 2016-01-10 19:50 Just an idea: If you are only interested in date, pass the datum as date, not datetime QuoteDataModule1.qryOnderhoud.FieldByName('Datum').AsDate := edt_Datum.Date;Maybe SQLite mixes up datetime/date somehow!? If you like to store the values as datetime also pass a time value. I use firebird there is no such problem. ---------------------------------------------------------------------- eny · 2016-01-10 21:37 What screenshot? ---------------------------------------------------------------------- rvk · 2016-01-10 22:22 Yes... we're missing the screenshot. But that's the problem with SQLite. It doesn't have a "DATE"-field. See https://www.sqlite.org/datatype3.html. It stores dates as TEXT, REAL or INTEGERs. Maybe these are mixed in your table which could lead to incorrect sorting. Another possibility is that you stored an incorrect DATE (i.e. 2015.31.12 or something) and SQLite stores it as just TEXT (and not seeing it as a DATE and consequently showing it as first record). SQLite doesn't check if you try to store the correct value. You can even store a string in a INTEGER column. (This is not a "bug" but a feature ) QuoteMost SQL database engines (every SQL database engine other than SQLite, as far as we know) uses static, rigid typing. With static typing, the datatype of a value is determined by its container - the particular column in which the value is stored. SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container. The dynamic type system of SQLite is backwards compatible with the more common static type systems of other database engines in the sense that SQL statement that work on statically typed databases should work the same way in SQLite. However, the dynamic typing in SQLite allows it to do things which are not possible in traditional rigidly typed databases. ---------------------------------------------------------------------- JanRoza · 2016-01-10 22:48 Sorry, forgot the screenshot, attached it now. @Getmem: SQLDB component does not have a property ASDATE, only ASDATETIME. @rvk: From the screenshot it looks as if SQLite stored them correctly since database manager SQLiteExpert is able to show them again in the correct format. The funny thing is that if I change the date of those first two records in the screenshot in SQLiteExpert they do sort correctly again. That's why I suspect that I'm not updating the record correctly from my coding but I cannot lay a finger on the fault. ---------------------------------------------------------------------- balazsszekely · 2016-01-11 06:03 Quote@JanRoza SQLDB component does not have a property ASDATE, only ASDATETIME. You can pass a value as Date. I did this with @madref once, he had similar problems. Please check this thread: http://forum.lazarus.freepascal.org/index.php/topic,30962.msg197462.ht ml#msg197462 ---------------------------------------------------------------------- JanRoza · 2016-01-12 06:18 Finally solved the problem. The database I used was converted from a delphi Absolute database and it turns out that the converted date fields looked like dates but were not really behaving like dates. Only records that were updated in my application did as expected. So I made a quick and dirty little program which updated all the date fields in all the records (via a dateedit field in a form) and now all dates sort as expected. Thanks for all your hints and advise!