[SOLVED]DATASET Doesn't Filter as Expected

[kito]

Hello
I am working on a program that calculates the resumption date after a certain number of days of suspension, based on the value in the DAYS field.

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

1. END = START+ DAYS

https://www3.0zz0.com/2023/07/17/19/582840362.png

The resumption date is on the next working day of END.date, excluding weekends
 

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

1.  
2. procedure TDataModule1.DatasetCalcFields(DataSet: TDataSet);  
3. begin
4.  if  (DayOfWeek(    END )=6 )then    resumption  := IncDay(END,2)   else resumption  := IncDay(END,1)  
5. end;

So far, everything is working fine. However, when I try to filter the days that match the resumption day, the program does not consider the condition and only gives me the next working day after the suspension end date Despite the values in the DATASET being correct.

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

1. Dataset.Filter:='resumption  like '+ QuotedStr( DateToStr( DateTimePicker.date)) ;
2.  Dataset.Filtered:=True;    

https://www3.0zz0.com/2023/07/17/19/892009578.png

Could you please help me with this issue?


LAZARUS 2.2.6  32bit
FPC 3.2.2
OS Win 10 64 bit

[alpine]

Not quite sure where the problem is, I can see a few potential pitfalls:

• like is a pattern matching operator, usually used with a char columns, not sure how it will behave on dates
• You are using a calculated field into the filter expression, probably it won't reach the DB engine (the field is unknown to the DB)
• You're using a field name (END) which is usually a SQL reserved word, it might be a problem

Why don't you use TDataset.OnFilterRecord to filter programmatically?  Even more so because you have calculated fields.

[kito]

thank you alpine for your replay

• like is a pattern matching operator, usually used with a char columns, not sure how it will behave on dates

It works pretty fine

• You are using a calculated field into the filter expression, probably it won't reach the DB engine (the field is unknown to the DB)

it's a data field  the calculated field is the number of days between START and End, I don't know if I made a mistake when I have incorporated it into this procedure my goal is to have the resumption   date changed automatically once I Change (START, END)

• You're using a field name (END) which is usually a SQL reserved word, it might be a problem

 All field names in the topic are aliases to approximate meaning and save time.

Why don't you use TDataset.OnFilterRecord to filter programmatically?  Even more so because you have calculated fields.

In fact, I use many filtering cases  , and I fear that  using   TDataset.OnFilterRecord  may limit my options.

[Zvoni]

Going out on a Limb:
DateTimePicker.Date is of Type TDate which is an Alias for TDateTime which is an alias for double.
He's using the LIKE-Operator without wildcards which translates to equality.

Now the question is: What DataType is "resumption"? What Database is underneath it all?
He's converting the Date to a string.

My guess is he's running afoul of equality comparisons of float's

[kito]

Hello Zvonic
I am using sqlite database, resumption is vchar, I am not facing any problem  using the  filter expression but it behave strangely when it comes  to the date that muchs the weekend. where I can't filter the dataset matching DateTimePicker.Date, but I must select a specific date(end+1 day)in order to see the results(end+2 days) The strange thing is that the date selected in the DateTimePicker is completely different from the results shown in the filtered dataset.

[Zvoni]

Hello Zvonic
I am using sqlite database, resumption is vchar, I am not facing any problem  using the  filter expression but it behave strangely when it comes  to the date that muchs the weekend. where I can't filter the dataset matching DateTimePicker.Date, but I must select a specific date(end+1 day)in order to see the results(end+2 days) The strange thing is that the date selected in the DateTimePicker is completely different from the results shown in the filtered dataset.

Wait a sec:
First: Which Format for your dates are you using in the SQLite-DB? I hope it's ISO-Format "YYYY-MM-DD"
Next: Have you checked that DateToStr returns the SAME Format?

I really, really hope you use ISO-Format

.....and there is no vchar DataType in SQLite.
It's TEXT.
Period

EDIT:

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

1. procedure TDataModule1.DatasetCalcFields(DataSet: TDataSet);  
2. begin
3.  if  (DayOfWeek(    END )=6 )then    resumption  := IncDay(END,2)   else resumption  := IncDay(END,1)  
4. end;

If resumption is of type TEXT (vchar), you do realize that IncDay returns a DateTime? And you're assigning without Formating!
--> https://www.freepascal.org/docs-html/rtl/sysutils/formatdatetime.html
I'm more surprised this compiles.....

[kito]

Wait a sec:
First: Which Format for your dates are you using in the SQLite-DB? I hope it's ISO-Format "YYYY-MM-DD"
Next: Have you checked that DateToStr returns the SAME Format?

I really, really hope you use ISO-Format

.....and there is no vchar DataType in SQLite.
It's TEXT.
Period


I'm more surprised this compiles.....

to avoid facing a problem with date encoding especially when I copy my program from one computer to another. Which naturally varies the date encoding, especially in the order of the day and the year  Therefore, I use the following solution/trick:
In the database: the field type is set as text/varchar,
 but in the program, I manually add a field to the dataset and assign it as a date type. Then, I link it to the relevant field, so the program handles it as a DateTime type
So I don't I have to worry about date time Format  issues .

[alpine]

to avoid facing a problem with date encoding especially when I copy my program from one computer to another. Which naturally varies the date encoding, especially in the order of the day and the year  Therefore, I use the following solution/trick:
In the database: the field type is set as text/varchar,
 but in the program, I manually add a field to the dataset and assign it as a date type. Then, I link it to the relevant field, so the program handles it as a DateTime type

And who is making the TDateTime<-->Text conversions between the 'linked' fields?

So I don't I have to worry about date time Format  issues .

You should, obviously.

Giving such kind of 'pseudo code' doesn't help. Consider a small project to demonstrate your issue.

[Zvoni]

Wait a sec:
First: Which Format for your dates are you using in the SQLite-DB? I hope it's ISO-Format "YYYY-MM-DD"
Next: Have you checked that DateToStr returns the SAME Format?

I really, really hope you use ISO-Format

.....and there is no vchar DataType in SQLite.
It's TEXT.
Period


I'm more surprised this compiles.....

to avoid facing a problem with date encoding especially when I copy my program from one computer to another. Which naturally varies the date encoding, especially in the order of the day and the year  Therefore, I use the following solution/trick:
In the database: the field type is set as text/varchar,
 but in the program, I manually add a field to the dataset and assign it as a date type. Then, I link it to the relevant field, so the program handles it as a DateTime type
So I don't I have to worry about date time Format  issues .

What utter non-sense.
Use ISO-Format "YYYY-MM-DD" in the Database, and any Date-Representation in your Frontend "converted" to that same ISO-Format in the background before writing/reading to/from the Database.
The ISO-Format WAS "invented" to address exactly the problem of "localized" Date-Representations

[kito]

And who is making the TDateTime<-->Text conversions between the 'linked' fields?

 I don't know how but as long as  I respect the date format every thing is fine.

You should, obviously.

Giving such kind of 'pseudo code' doesn't help. Consider a small project to demonstrate your issue.

good idea! thanks  here you are :

[kito]

What utter non-sense.
Use ISO-Format "YYYY-MM-DD" in the Database, and any Date-Representation in your Frontend "converted" to that same ISO-Format in the background before writing/reading to/from the Database.
The ISO-Format WAS "invented" to address exactly the problem of "localized" Date-Representations

In bilingual countries, especially, you could find an individual using two different encodings if he own two devices. Some may use a different date format (yyyy-mm-dd" / "dd-mm-yyyy"), while others might utilize a different date separator ("/"or "-" or ".") on their device. Often, the program stops execution when it encounters such discrepancies, displaying error messages like "Unknown date" or "Invalid encoding." Therefore, I resort to storing date-related data as text to avoid such issues.

I think you give me a hint because I am using a different date separator on my pc (-) and the registered dates on the database (/)
besides the attached project works fine 

[alpine]

The attached Zip is empty.

[kito]

The attached Zip is empty.

https://drive.google.com/file/d/1Dqj-tRZlMfRsz52pUocM1SkP2EBSZZVe/view?usp=sharing

[alpine]

Can't download the shared file.
I'd recommend to clean the project before zipping.

[kito]

Can't download the shared file.
I'd recommend to clean the project before zipping.

Thankful for your time .I am very sorry for the delay.
google said it may contain a harmful script or something and needs to be reviewed   

please try this :

https://drive.google.com/file/d/1cfl77-GR2lHuYZQuqodiujkE9wMzvcrJ/view?usp=sharing