[SOLVED]DATASET Doesn't Filter as Expected

[alpine]

Same thing.

Please, do not include the executable file and the lib directory into the zip. They'll be built either way. The SQLite DLLs are publicly available and probably I have them already.

[kito]

Same thing.

Please, do not include the executable file and the lib directory into the zip. They'll be built either way. The SQLite DLLs are publicly available and probably I have them already.

I'm already learning a lot thanks

[wp]

Please don't upload the project to google drive anyway because it will be gone after some time, and when somebody revisits this thread later he cannot find the attachment any more.

It is much better to attach your code directly to the forum: Create a zip file containing ONLY (!) the .pas, .lfm, .lpi and .lpr files of your project, as well as the database file. Then go to "Attachments and other options" on the forum site, right below the edit box, and attach the zip file to the post. Be aware that there is an upload size limit of 500 KB which is absolutely enough for source code (but not if you include binary files created by the IDE).

[alpine]

Same thing.

Please, do not include the executable file and the lib directory into the zip. They'll be built either way. The SQLite DLLs are publicly available and probably I have them already.

I'm already learning a lot thanks

I can't open the rar file (7-Zip says: Can not open file as archive). Please, do as wp instructed, make a ZIP file and attach it.

[kito]

Please don't upload the project to google drive anyway because it will be gone after some time, and when somebody revisits this thread later he cannot find the attachment any more.

It is much better to attach your code directly to the forum: Create a zip file containing ONLY (!) the .pas, .lfm, .lpi and .lpr files of your project, as well as the database file. Then go to "Attachments and other options" on the forum site, right below the edit box, and attach the zip file to the post. Be aware that there is an upload size limit of 500 KB which is absolutely enough for source code (but not if you include binary files created by the IDE).

Hello hello WP thanks for your intervention

I can't open the rar file (7-Zip says: Can not open file as archive). Please, do as wp instructed, make a ZIP file and attach it.

I owe you a cup of coffee 

[wp]

I don't understand what you want to see.

You filter on the DREP column, and it is my impression that this is working correctly: When I select in the DateTimePicker the date 22.01.2023 the two rows appear which have exactly this date in the DREP column.

Even when I change the DefaultFormatSettings.ShortDateFormat and .DateSeparator it still works.

[kito]

I don't understand what you want to see.

You filter on the DREP column, and it is my impression that this is working correctly: When I select in the DateTimePicker the date 22.01.2023 the two rows appear which have exactly this date in the DREP column.

Even when I change the DefaultFormatSettings.ShortDateFormat and .DateSeparator it still works.

As mentioned earlier, this mini-application works fine, but the original project, which contains a database with thousands of records, has an issue that I'm trying to identify. I also uploaded the mini-application to show alpin and Zvoni the type of fields I used in the database and the way I converted the data types between the database and the program, and how I used the procedure datasetCalcFields to update no calculable field . perhaps there is some notes  and suggestions about the method .

[Zvoni]

I repeat: Don't use DateToStr!!!
untested

Code: Pascal  [Select][+][-]

1. procedure TForm1.DateTimePicker1Change(Sender: TObject);
2. begin
3.    DataModule1.ZQuery1.Filtered:=false;
4.    DataModule1.ZQuery1.Filter:='DREP = '+ QuotedStr(FormatDateTime('YYYY-MM-DD', DateTimePicker1.date)) ;
5.   DataModule1.ZQuery1.Filtered:=True;
6. end;

And read the SQLite-documentation

Code: SQL  [Select][+][-]

1. CREATE TABLE "VAC" (
2.         "ID"    INTEGER,
3.         "ST"    VARCHAR(50) ,
4.         "ED"    VARCHAR(50) ,
5.         "CO"    INTEGER,
6.         "DREP"  VARCHAR(50) ,
7.         PRIMARY KEY("ID" AUTOINCREMENT)
8. )

There is no VARCHAR-DataType
VARCHAR(50) --> the "50" is completly ignored by SQLite.
Use TEXT and be done with it

EDIT2: And don't use AUTOINCREMENT on your Integer-Primary Key

[SymbolicFrank]

Use these:

Code: Pascal  [Select][+][-]

1. function ISODateTime(const ADate: TDateTime; AddT: Boolean = False): string;
2. begin
3.   if AddT then
4.     Result := FormatDateTime('yyyy-mm-dd"T"hh:mm:ss', ADate)
5.   else
6.     Result := FormatDateTime('yyyy-mm-dd hh:mm:ss', ADate);
7. end;
8.  
9. function ISODateTime(const DateString: string; ReturnUTC : Boolean = False):
10.   TDateTime;
11. begin
12.   TryISO8601ToDate(DateString, Result, ReturnUTC);
13. end;

As Zvoni said, only use ISO dates in the database. And only use TDateTime in your code, they will be converted to the right format when you display them. Mixing Date-Time formats always results in problems.

[alpine]

As Zvoni said: never use data/time conversion routines, at least, not without giving it an explicit format string!

I noticed you have using Zeos components and declared TDateField fields. Since sqlite doesn't have corresponding types for date/time, I can only speculate how Zeos converts them internally. It may even use DateToStr() as you do. Anyway, you can never be sure with, unless you unify all your conversions to a known representation. ISO-8601 is OK since it is lexicographic comparable at DB level.

So, you can replace your TDateField fields with TStringField and convert them to/from ISO-8601 on each access.
You may choose other field type - TLargeintField for unix time for example, or TFloatField for directly storing the TDateTime values. Then processing will be much easier, but with the disadvantage that you can't easily read them by eye. 

And don't use LIKE operator, it's not meant for that.

[kito]

I repeat: Don't use DateToStr!!!
untested

Code: Pascal  [Select][+][-]

1. procedure TForm1.DateTimePicker1Change(Sender: TObject);
2. begin
3.    DataModule1.ZQuery1.Filtered:=false;
4.    DataModule1.ZQuery1.Filter:='DREP = '+ QuotedStr(FormatDateTime('YYYY-MM-DD', DateTimePicker1.date)) ;
5.   DataModule1.ZQuery1.Filtered:=True;
6. end;

I did a small test and it works, You are right, the method being correct is more important than  just getting the result right. I am changing some lines as you suggested.

And read the SQLite-documentation

Code: SQL  [Select][+][-]

1. CREATE TABLE "VAC" (
2.         "ID"    INTEGER,
3.         "ST"    VARCHAR(50) ,
4.         "ED"    VARCHAR(50) ,
5.         "CO"    INTEGER,
6.         "DREP"  VARCHAR(50) ,
7.         PRIMARY KEY("ID" AUTOINCREMENT)
8. )

There is no VARCHAR-DataType
VARCHAR(50) --> the "50" is completly ignored by SQLite.
Use TEXT and be done with it

If I use text type every text field turns to the word "memo" rather than the string in the database and the number besides Varchar it represents the max length   (I tested it  )and  SQLite behaves according to it. 

EDIT2: And don't use AUTOINCREMENT on your Integer-Primary Key

I do not prefer manual input for the primary key. Can you tell me why, if you please?

[wp]

Code: Pascal  [Select][+][-]

1.    DataModule1.ZQuery1.Filter:='DREP = '+ QuotedStr(FormatDateTime('YYYY-MM-DD', DateTimePicker1.date)) ;
2. end;

I understood your initial post such that you want to filter on the calculated field. Looking at the fields in the Object Inspector, I see that the calculated field is ED, DREP has FieldKind = fkData, but you are changing it in the OnCalcFields event of the dataset. This looks strange, and I've never seen that.

Next: when I change the filter condition above such that the field ED is filtered, filtering does not work any more. The same happens with the field DREP when I redeclare its FieldKind as fkCalculated.

[kito]

Use these:

Code: Pascal  [Select][+][-]

1. function ISODateTime(const ADate: TDateTime; AddT: Boolean = False): string;
2. begin
3.   if AddT then
4.     Result := FormatDateTime('yyyy-mm-dd"T"hh:mm:ss', ADate)
5.   else
6.     Result := FormatDateTime('yyyy-mm-dd hh:mm:ss', ADate);
7. end;
8.  
9. function ISODateTime(const DateString: string; ReturnUTC : Boolean = False):
10.   TDateTime;
11. begin
12.   TryISO8601ToDate(DateString, Result, ReturnUTC);
13. end;

As Zvoni said, only use ISO dates in the database. And only use TDateTime in your code, they will be converted to the right format when you display them. Mixing Date-Time formats always results in problems.

Hello SymbolicFrank

 Yes,I am certain that the root cause of my main problem is mixing between different formats of dates  I was dealing with Date-Time objects in a chaotic manner.

[kito]

As Zvoni said: never use data/time conversion routines, at least, not without giving it an explicit format string!

I noticed you have using Zeos components and declared TDateField fields. Since sqlite doesn't have corresponding types for date/time, I can only speculate how Zeos converts them internally. It may even use DateToStr() as you do. Anyway, you can never be sure with, unless you unify all your conversions to a known representation. ISO-8601 is OK since it is lexicographic comparable at DB level.

Just as a question Zvoni will kill me , what's the harm in preferring to deal with type string instead of dates, when no one complains about the texts?   Especially since Sqlite does not differentiate between them why does the function date to string exist at all?
as SymbolicFrank said
 

Mixing Date-Time formats always results in problems.

All I was trying to do is avoid dealing with date-related issues, so I converted everything into strings. I even used TDateTimePicker. I avoided writing dates manually to correctly keep the Format , and by the way, I used "Like" instead of "=" because earlier I wanted the ability to filter based on the month and year /or just year. I was using a Tedit before TDateTimePicker .

[TRon]

All I was trying to do is avoid dealing with date-related issues, so I converted everything into strings.

... and that is exactly when/where all the horror starts happening.

If you are not aware that every user can set his/her own date-time format and not account for it by explicitly having the strings formatted in a format that is consistent throughout your application then things go wrong very fast.

Have you (n)ever noticed how f.e. americans visualize dates in comparison to europeans ? It is a nightmare to work with if you do not know how to handle such things properly in a database and with every conversion you make be it from string to datetime or vise verse or trying to represent the date as a string in another display format then also there things go wrong very quick.

In your specific case you do not even require the time portion so you could just as well have used integers to store the actual date, f.e. 20031202 for december 2 2003 as that would allow for easy sorting, checking for ranges or checking if one date is before or after another date.

But the important part to remember is to never ever work with dates as strings in order to not run into such issues to begin with.