[SOLVED]DATASET Doesn't Filter as Expected lazarus / 63-Database ---------------------------------------------------------------------- kito · 2023-07-17 19:55 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i  procedure TDataModule1.DatasetCalcFields(DataSet: TDataSet);   begin if  (DayOfWeek(    END )=6 )then    resumption  := IncDay(END,2)   else resumption  := IncDay(END,1)  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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i Dataset.Filter:='resumption  like '+ QuotedStr( DateToStr( DateTimePicker.date)) ; 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 · 2023-07-17 20:52 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 datesYou 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 · 2023-07-17 21:31 thank you alpine for your replay Quotelike is a pattern matching operator, usually used with a char columns, not sure how it will behave on datesIt works pretty fine QuoteYou 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) QuoteYou'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. QuoteWhy 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 · 2023-07-18 06:41 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 · 2023-07-18 07:30 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 · 2023-07-18 07:53 Quote from: kito on July 18, 2023, 09:30:07 amHello 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TDataModule1.DatasetCalcFields(DataSet: TDataSet);  begin if  (DayOfWeek(    END )=6 )then    resumption  := IncDay(END,2)   else resumption  := IncDay(END,1)  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 · 2023-07-18 10:53 Quote from: Zvoni on July 18, 2023, 09:53:12 am 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 · 2023-07-18 11:32 Quote from: kito on July 18, 2023, 12:53:51 pmto 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? Quote from: kito on July 18, 2023, 12:53:51 pmSo 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 · 2023-07-18 11:40 Quote from: kito on July 18, 2023, 12:53:51 pmQuote from: Zvoni on July 18, 2023, 09:53:12 am 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 · 2023-07-18 17:15 QuoteAnd 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. QuoteYou should, obviously. QuoteGiving such kind of 'pseudo code' doesn't help. Consider a small project to demonstrate your issue. good idea! thanks  here you are : ---------------------------------------------------------------------- kito · 2023-07-18 17:30 QuoteWhat 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 · 2023-07-18 17:33 The attached Zip is empty. ---------------------------------------------------------------------- kito · 2023-07-18 17:54 Quote from: alpine on July 18, 2023, 07:33:52 pmThe attached Zip is empty. https://drive.google.com/file/d/1Dqj- tRZlMfRsz52pUocM1SkP2EBSZZVe/view?usp=sharing ---------------------------------------------------------------------- alpine · 2023-07-18 21:13 Can't download the shared file. I'd recommend to clean the project before zipping. ---------------------------------------------------------------------- kito · 2023-07-19 20:13 Quote from: alpine on July 18, 2023, 11:13:09 pmCan'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 ---------------------------------------------------------------------- alpine · 2023-07-19 20:52 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 · 2023-07-19 21:04 Quote from: alpine on July 19, 2023, 10:52:36 pmSame 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 · 2023-07-19 21:35 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 · 2023-07-19 21:45 Quote from: kito on July 19, 2023, 11:04:59 pmQuote from: alpine on July 19, 2023, 10:52:36 pmSame 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 · 2023-07-19 22:00 Quote from: wp on July 19, 2023, 11:35:42 pmPlease 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 Quote from: alpine on July 19, 2023, 11:45:26 pm 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 · 2023-07-19 22:32 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 · 2023-07-19 23:29 Quote from: wp on July 20, 2023, 12:32:17 amI 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 · 2023-07-20 06:58 I repeat: Don't use DateToStr!!! untested 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 procedure TForm1.DateTimePicker1Change(Sender: TObject);begin   DataModule1.ZQuery1.Filtered:=false;   DataModule1.ZQuery1.Filter:='DREP = '+ QuotedStr(FormatDateTime('YYYY-MM-DD', DateTimePicker1.date)) ;  DataModule1.ZQuery1.Filtered:=True;end; And read the SQLite-documentation Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i CREATE TABLE "VAC" (        "ID"    INTEGER,        "ST"    VARCHAR(50) ,      "ED"    VARCHAR(50) ,        "CO"    INTEGER,        "DREP"  VARCHAR(50) ,        PRIMARY KEY("ID" AUTOINCREMENT))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 · 2023-07-20 14:27 Use these: 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 function ISODateTime(const ADate: TDateTime; AddT: Boolean = False): string;begin  if AddT then    Result := FormatDateTime('yyyy-mm- dd"T"hh:mm:ss', ADate)  else    Result := FormatDateTime('yyyy-mm-dd hh:mm:ss', ADate);end; function ISODateTime(const DateString: string; ReturnUTC : Boolean = False):  TDateTime;begin  TryISO8601ToDate(DateString, Result, ReturnUTC);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 · 2023-07-20 15:05 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 · 2023-07-20 22:20 Quote from: Zvoni on July 20, 2023, 08:58:12 amI repeat: Don't use DateToStr!!! untested 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 procedure TForm1.DateTimePicker1Change(Sender: TObject);begin   DataModule1.ZQuery1.Filtered:=false;   DataModule1.ZQuery1.Filter:='DREP = '+ QuotedStr(FormatDateTime('YYYY-MM-DD', DateTimePicker1.date)) ;  DataModule1.ZQuery1.Filtered:=True;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. Quote from: Zvoni on July 20, 2023, 08:58:12 amAnd read the SQLite- documentation Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i CREATE TABLE "VAC" (        "ID"    INTEGER,        "ST"    VARCHAR(50) ,      "ED"    VARCHAR(50) ,        "CO"    INTEGER,        "DREP"  VARCHAR(50) ,        PRIMARY KEY("ID" AUTOINCREMENT))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.  QuoteEDIT2: 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 · 2023-07-20 22:58 Quote from: kito on July 21, 2023, 12:20:31 amCode: 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.ZQuery1.Filter:='DREP = '+ QuotedStr(FormatDateTime('YYYY-MM-DD', DateTimePicker1.date)) ;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 · 2023-07-21 00:08 Quote from: SymbolicFrank on July 20, 2023, 04:27:06 pmUse these: 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 function ISODateTime(const ADate: TDateTime; AddT: Boolean = False): string;begin  if AddT then    Result := FormatDateTime('yyyy-mm- dd"T"hh:mm:ss', ADate)  else    Result := FormatDateTime('yyyy-mm-dd hh:mm:ss', ADate);end; function ISODateTime(const DateString: string; ReturnUTC : Boolean = False):  TDateTime;begin  TryISO8601ToDate(DateString, Result, ReturnUTC);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 · 2023-07-21 00:54 Quote from: alpine on July 20, 2023, 05:05:25 pmAs 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  QuoteMixing 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 · 2023-07-21 01:50 Quote from: kito on July 21, 2023, 02:54:51 amAll 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. ---------------------------------------------------------------------- Zvoni · 2023-07-21 06:23 Quote from: kito on July 21, 2023, 12:20:31 am Quote from: Zvoni on July 20, 2023, 08:58:12 amAnd read the SQLite- documentation Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i CREATE TABLE "VAC" (        "ID"    INTEGER,        "ST"    VARCHAR(50) ,      "ED"    VARCHAR(50) ,        "CO"    INTEGER,        "DREP"  VARCHAR(50) ,        PRIMARY KEY("ID" AUTOINCREMENT))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.  Ah. Damn. Forgot about that one. Well it's easy to solve Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT CHAR(SomeTextField) AS MyField1 FROM MyTable....And all those "Memos" go away As for the "maximum" length: SQLite ignores it. Even if you define a Column as VARCHAR(50), SQLite will not prevent you from inserting a String with 200 Characters QuoteQuoteEDIT2: 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? You don't have to provide a "manual" Primary Key. Without AUTOINCREMENT SQLite will still "count up" your Primary Key. The Difference being, that if in case you reach the upper end of the available Value-Range (as unlikely that might be), SQLite will start back at the bottom again, and use deleted values QuoteJust 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 Because SQLite has NO dedicated DateTime DataType. With SQLite you have three options for Dates: TEXT --> "2023-07-21" --> ISO-DateFOrmat REAL --> 42536.25689 --> JulianDay INTEGER --> 123456789 --> UNIXEPOCH DateToStr is for Situations you want to Display a Date (which in the Background is a Double) in a Control, which accepts only Strings. ---------------------------------------------------------------------- Zvoni · 2023-07-21 07:24 OK, next. I'm trying to figure out, what he's actually trying to achieve in his CalcFields-Routine. Could you explain (with examples) what you're trying here? EDIT: OK, trying to make sense of it in his sample DB the first record is as follows: ST = 2023-01-02 CO = 18 (Where's that value coming from?) I'm going to ignore any Values in the DB for ED and DREP If i understand it correctly, it goes like this: ED = ST + CO-1 --> 2023-01-02 + 18-1 = 2023-01-19 DREP = ED+1 --> 2023-01-19 + 1 = 2023-01-20 [INSERT] https://www.freepascal.org/docs-html/rtl/sysutils/dayofweek.html QuoteDayOfWeek returns the day of the week from DateTime. Sunday is counted as day 1, Saturday is counted as day 7. In your CalcFields you check if DayOfWeek(DREP) = 6 --> You check for Friday [INSERT END] If DREP = Friday Then DREP = DREP + 2 = 2023-01-20 + 2 = 2023-01-22 (Which would make it a Sunday)--> Which is also in his DB (The Value in the DB for ED makes no sense whatsoever) Correct so far? I'm pretty sure this calculation can be solved in SQLite directly as a computed column. Then you could throw your CalcField routine into the bin ---------------------------------------------------------------------- TRon · 2023-07-21 08:03 @ZVoni: My "to human language" translated attempt: "something" has a start and end date, occupying a certain amount of days between them. After the amount of days that 'something' needs to be resumed (I thought of it as for example sending a notice or resuming payments) If the end date is at the start of a weekend then 'resuming' can't take place (people are not present in weekends) in which case the end- date (or actually resume date) has to  shift over an x amount of days so that resuming takes place f.e. during office hours/days (people are present again). Quote from: Zvoni on July 21, 2023, 09:24:13 amI'm pretty sure this calculation can be solved in SQLite directly as a computed column. Then you could throw your CalcField routine into the bin Exactly my thought as well. No idea why that is a separate column stored in the DB unless that is the whole point for the code to exist (to update a database with additional/corrected information) or perhaps someone does not trust the calculation(s) and need to be stored as intermediate result. ---------------------------------------------------------------------- Zvoni · 2023-07-21 08:06 Quote from: TRon on July 21, 2023, 10:03:56 am@ZVoni: My "to human language" translated attempt: "something" has a start and end date, occupying a certain amount of days between them. After the amount of days that 'something' needs to be resumed (I thought of it as for example sending a notice or resuming payments) If the end date is at the start of a weekend then 'resuming' can't take place (people are not present in weekends) in which case the end- date (or actually resume date) has to  shift over an x amount of days so that resuming takes place f.e. during office hours/days (people are present again). Quote from: Zvoni on July 21, 2023, 09:24:13 amI'm pretty sure this calculation can be solved in SQLite directly as a computed column. Then you could throw your CalcField routine into the bin Exactly my thought as well. No idea why that is a separate column stored in the DB unless that is the whole point for the code to exist (to update a database with additional/corrected information) or perhaps someone does not trust the calculation(s) and need to be stored as intermediate result. Ron, i understood that, too. But his DayOfWeek=6 doesn't make sense in that case this is mystifying me completly: QuoteIf i understand it correctly, it goes like this: ED = ST + CO-1 --> 2023-01-02 + 18-1 = 2023-01-19 DREP = ED+1 --> 2023-01-19 + 1 = 2023-01-20 Why not just DREP = ST + CO Because that's what math is for That said: I understand that if a Startdate is e.g. 2023-07-01 and you add 18 days to it, math says that EndDate is 2023-07-19 BUT If something starts on 2023-07-01 that day counts for it, too! So EndDate would be StartDate + 18-1 = 2023-07-18 Something starts on the First, it takes 18 days, so it finishes on 18th Understood so far, but then i don't understand, why he adds a Day again? Because that would be a "new" StartDate, right? resp. his "Resuming Date"? QuoteIf the end date is at the start of a weekend then 'resuming' can't take place (people are not present in weekends) in which case the end-date (or actually resume date) has to  shift over an x amount of days so that resuming takes place f.e. during office hours/days (people are present again). Uhmm, no. As far as i inderstood: If the "resuming Day" is on a Weekend (why isn't he checking if it's a Sunday?), then move forward 2 days He's checking DREP=6  not ED=6 ---------------------------------------------------------------------- TRon · 2023-07-21 08:25 Quote from: Zvoni on July 21, 2023, 10:06:15 amBut his DayOfWeek=6 doesn't make sense in that case True, unless 'their weekend' starts at the 6-th day. But I am tempted to go with your thought in thinking it might perhaps have been a mistake. Noting that some countries also use sunday and others monday as first day of the week. Quote... Why not just DREP = ST + CO Because that's what math is for true. But that could have been a honest mistake in not realizing if the resulting math needs to include or exclude the last day. QuoteUnderstood so far, but then i don't understand, why he adds a Day again? Because that would be a "new" StartDate, right? resp. his "Resuming Date"? Same reason as above. Does the resume takes place on the day itself or a day later and did the previous calculation include that day already ? QuoteUhmm, no. As far as i inderstood: If the "resuming Day" is on a Weekend (why isn't he checking if it's a Sunday?), then move forward 2 days He's checking DREP=6  not ED=6 True, in that regards I made a mistake so thank you for correcting. But tbh just like you I am puzzled. If I have a choice then I would prefer to use your tagline  But perhaps TS is able to shed a (more detailed) light ? ---------------------------------------------------------------------- kito · 2023-07-21 08:35 Quote from: wp on July 21, 2023, 12:58:55 amQuote from: kito on July 21, 2023, 12:20:31 amCode: 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.ZQuery1.Filter:='DREP = '+ QuotedStr(FormatDateTime('YYYY-MM-DD', DateTimePicker1.date)) ;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. I think because the  Fieldkind :fkCalculated . Only the fkInternalCalc and fkAggregate fields can be used in filtering . and yes  DREP changed and updated  when the dataset is not even in update or edit mode ! ---------------------------------------------------------------------- Zvoni · 2023-07-21 08:36 Quote from: TRon on July 21, 2023, 10:25:43 amQuote from: Zvoni on July 21, 2023, 10:06:15 amBut his DayOfWeek=6 doesn't make sense in that case True, unless 'their weekend' starts at the 6-th day. But I am tempted to go with your thought in thinking it might perhaps have been a mistake. Noting that some countries also use sunday and others monday as first day of the week. That wouldn't matter, since DayOfWeek checks against a (hardcoded) enumeration. At least i think it's hardcoded, because otherwise the documentation doesn't make sense. To compare: SQLite treats "Weekdays"  as Sunday=0......Saturday=6 Ah, well depending on OP's answer, i already have my "solution" as computed columns done. "ED = ST+CO-1" "If the next resuming day (ED+1) is a Saturday or a Sunday, move forward to the next monday, else next resuming day is ED+1" see attached (Table VAC2) EDIT: Needs SQLite 3.31 or later ---------------------------------------------------------------------- TRon · 2023-07-21 08:52 Quote from: Zvoni on July 21, 2023, 10:36:35 amThat wouldn't matter, since DayOfWeek checks against a (hardcoded) enumeration. At least i think it's hardcoded, because otherwise the documentation doesn't make sense. To compare: SQLite treats "Weekdays"  as Sunday=0......Saturday=6 All true. But think of it as being an american all your life and for the first time reading an european date notation (or vise verse). First line of thought would be to make sense of it how it was learned and used for all;those years. Same goes for first day of the week (even though the documentation explicitly states otherwise). I have no idea where TS originates from (and it also does not matter to know) but it might be a simple misunderstanding or miscommunication. We do not all share the same believes (thank heavens for that, and thank writers for their documentation  ). QuoteAh, well depending on OP's answer, i already have my "solution" as computed columns done. Bravo, nicely done ! ---------------------------------------------------------------------- kito · 2023-07-21 08:57 Quote from: TRon on July 21, 2023, 03:50:01 am ... 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. Hello TRon I am now convinced this is indisputable and cannot be denied. It is reason enough to set things right Quote from: TRon on July 21, 2023, 03:50:01 amIn 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. interesting! Quote from: TRon on July 21, 2023, 03:50:01 amBut 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. I understand now. It  should be a comprehensive review and take into account various circumstances before writing codes, thank you ! ---------------------------------------------------------------------- Zvoni · 2023-07-21 09:18 Quote from: kito on July 21, 2023, 10:57:00 amQuote from: TRon on July 21, 2023, 03:50:01 amIn 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. interesting! Yes for sorting, No for everything else. In the company i work for, we actually have exactly that Format. And if you don't know, that it's NOT of DataType DATE, you fall afoul of 20041202 - 20031202 --> expected Result being 366 (2004 was LeapYear).... instead of 10000 You'd just replace one problem with another Has been driving me up the wall to write the SELECT's for that, CASTing to/from and whatnot ---------------------------------------------------------------------- kito · 2023-07-21 09:34 Quote from: Zvoni on July 21, 2023, 09:24:13 amOK, next. I'm trying to figure out, what he's actually trying to achieve in his CalcFields-Routine. Could you explain (with examples) what you're trying here? Here is the story, in brief, starting from the beginning: "There is a group of workers who are employed on fixed-term contracts. Each worker is entitled to days off each month(at least 16 days from the first month) in contract = 3.33 days off, that is where CO comes from CO = Total of days off.   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   function TDataModule1.CONJ(A:TDate;B:TDate):float   ;    var      s:float   ;      v:string;    begin         if (DayOf(A)) < 16 then      begin     v := ((MonthsBetween(B,A,True)+1)*3.33).ToString;       end     else      v := ((MonthsBetween(B,A,True))*3.33).ToString;     s:= round(StrToFloat(v));    Result := s ;      end;          the vacation begins on ST and ends on ED, while DREP (date_de_reprise) is the day of resumption and must be a working day. Taking into account that the weekend in my region consists of Friday and Saturday, the worker cannot resume work during these two days.  I wanted the interface to be as simple as possible, where interaction is done through a dbgrid. Therefore, I avoided making the user go up or down to edit or add anything, just like how we deal with tables in spreadsheets ---------------------------------------------------------------------- kito · 2023-07-21 09:49 Quote from: Zvoni on July 21, 2023, 09:24:13 am in his sample DB the first record is as follows: ST = 2023-01-02 CO = 18 (Where's that value coming from?) I'm going to ignore any Values in the DB for ED and DREP If i understand it correctly, it goes like this: ED = ST + CO-1 --> 2023-01-02 + 18-1 = 2023-01-19 DREP = ED+1 --> 2023-01-19 + 1 = 2023-01-20 [INSERT] https://www.freepascal.org/docs-html/rtl/sysutils/dayofweek.html QuoteDayOfWeek returns the day of the week from DateTime. Sunday is counted as day 1, Saturday is counted as day 7. In your CalcFields you check if DayOfWeek(DREP) = 6 --> You check for Friday [INSERT END] If DREP = Friday Then DREP = DREP + 2 = 2023-01-20 + 2 = 2023-01-22 (Which would make it a Sunday)--> Which is also in his DB (The Value in the DB for ED makes no sense whatsoever) Correct so far? I'm pretty sure this calculation can be solved in SQLite directly as a computed column. Then you could throw your CalcField routine into the bin Quote from: TRon on July 21, 2023, 10:03:56 am@ZVoni: My "to human language" translated attempt: "something" has a start and end date, occupying a certain amount of days between them. After the amount of days that 'something' needs to be resumed (I thought of it as for example sending a notice or resuming payments) If the end date is at the start of a weekend then 'resuming' can't take place (people are not present in weekends) in which case the end- date (or actually resume date) has to  shift over an x amount of days so that resuming takes place f.e. during office hours/days (people are present again). Quote from: Zvoni on July 21, 2023, 09:24:13 amI'm pretty sure this calculation can be solved in SQLite directly as a computed column. Then you could throw your CalcField routine into the bin Exactly my thought as well. No idea why that is a separate column stored in the DB unless that is the whole point for the code to exist (to update a database with additional/corrected information) or perhaps someone does not trust the calculation(s) and need to be stored as intermediate result. I am very impressed with your ability to analyze the system through just a few lines of code. ---------------------------------------------------------------------- Zvoni · 2023-07-21 09:49 Quote from: kito on July 21, 2023, 11:34:10 amHere is the story, in brief, starting from the beginning: "There is a group of workers who are employed on fixed-term contracts. Each worker is entitled to days off each month(at least 16 days from the first month) in contract = 3.33 days off, that is where CO comes from CO = Total of days off. 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   function TDataModule1.CONJ(A:TDate;B:TDate):float   ;    var      s:float   ;      v:string;    begin      if (DayOf(A)) < 16 then      begin     v := ((MonthsBetween(B,A,True)+1)*3.33).ToString;      end     else    v := ((MonthsBetween(B,A,True))*3.33).ToString;     s:= round(StrToFloat(v));    Result := s ;    end;           the vacation begins on ST and ends on ED, while DREP (date_de_reprise) is the day of resumption and must be a working day. Taking into account that the weekend in my region consists of Friday and Saturday, the worker cannot resume work during these two days.Now we're getting somewhere! Meaning, your first working day of the week is actually the Sunday! Quote I wanted the interface to be as simple as possible, where interaction is done through a dbgrid. Therefore, I avoided making the user go up or down to edit or add anything, just like how we deal with tables in spreadsheets adjusted VAC2-Table in attachment How to use: You only INSERT/UPDATE Fields ST and CO,  ED and DREP are recalculated everytime you ACCESS those two fields (a.k.a reading/SELECTing them). You don't need youor "CalcField" anymore Just look at VAC2 directly with any viewer of your choice. As said: Requires SQLite 3.31 or later ---------------------------------------------------------------------- wp · 2023-07-21 09:51 Quote from: kito on July 21, 2023, 10:35:11 amOnly the fkInternalCalc and fkAggregate fields can be used in filtering . I would not call myself a database guru, but I doubt that this statement is correct in general. The attached project uses a TBufDataset which has three predefined fields, IntField, Floatfield and TextField, plus a calculated field, IntFieldPlus1, which adds 1 to the IntField value. As you can see, filtering on the fkCalculated IntFieldPlus1 field works perfectly here. The next step would be to study a SQLite3 database in the same way, in both LCL SqlDB and in the ZEOS implementations. Unfortunately working with persistent fields in the IDE is quite a mess... ---------------------------------------------------------------------- alpine · 2023-07-21 10:02 Quote from: kito on July 21, 2023, 11:34:10 am"There is a group of workers who are employed on fixed-term contracts. Each worker is entitled to days off each month(at least 16 days from the first month) in contract = 3.33 days off, that is where CO comes from CO = Total of days off.   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   function TDataModule1.CONJ(A:TDate;B:TDate):float   ;    var      s:float   ;      v:string;    begin         if (DayOf(A)) < 16 then      begin     v := ((MonthsBetween(B,A,True)+1)*3.33).ToString;       end     else      v := ((MonthsBetween(B,A,True))*3.33).ToString;     s:= round(StrToFloat(v));    Result := s ;      end;          Side note: why v:=().ToString and then s:= round(StrToFloat(v)) ? Quote from: kito on July 21, 2023, 11:34:10 am the vacation begins on ST and ends on ED, while DREP (date_de_reprise) is the day of resumption and must be a working day. Taking into account that the weekend in my region consists of Friday and Saturday, the worker cannot resume work during these two days. I don't want to complicate things further, but 'workday' is not the same as 'not weekend'. There are also official holidays, some of them not fixed at a date. Additional 'workday' calendar should be maintained separately. ---------------------------------------------------------------------- kito · 2023-07-21 10:10 Quote from: Zvoni on July 21, 2023, 11:49:37 amQuote from: kito on July 21, 2023, 11:34:10 amHere is the story, in brief, starting from the beginning: "There is a group of workers who are employed on fixed-term contracts. Each worker is entitled to days off each month(at least 16 days from the first month) in contract = 3.33 days off, that is where CO comes from CO = Total of days off. 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   function TDataModule1.CONJ(A:TDate;B:TDate):float   ;    var      s:float   ;      v:string;    begin      if (DayOf(A)) < 16 then      begin     v := ((MonthsBetween(B,A,True)+1)*3.33).ToString;      end     else    v := ((MonthsBetween(B,A,True))*3.33).ToString;     s:= round(StrToFloat(v));    Result := s ;    end;           the vacation begins on ST and ends on ED, while DREP (date_de_reprise) is the day of resumption and must be a working day. Taking into account that the weekend in my region consists of Friday and Saturday, the worker cannot resume work during these two days.Now we're getting somewhere! Meaning, your first working day of the week is actually the Sunday! Quote I wanted the interface to be as simple as possible, where interaction is done through a dbgrid. Therefore, I avoided making the user go up or down to edit or add anything, just like how we deal with tables in spreadsheets adjusted VAC2-Table in attachment How to use: You only INSERT/UPDATE Fields ST and CO,  ED and DREP are recalculated everytime you ACCESS those two fields (a.k.a reading/SELECTing them). You don't need youor "CalcField" anymore Just look at VAC2 directly with any viewer of your choice. As said: Requires SQLite 3.31 or later Wow    I can't believe my eyes   I didn't expect, or even think, that it could be possible to achieve this  with SQLite thank you  Zvonic you made my day! ---------------------------------------------------------------------- Zvoni · 2023-07-21 10:29 Quote from: alpine on July 21, 2023, 12:02:24 pmI don't want to complicate things further, but 'workday' is not the same as 'not weekend'. There are also official holidays, some of them not fixed at a date. Additional 'workday' calendar should be maintained separately. Be quiet!  Don't open another can of worms ---------------------------------------------------------------------- kito · 2023-07-21 10:41 Quote from: wp on July 21, 2023, 11:51:19 amQuote from: kito on July 21, 2023, 10:35:11 amOnly the fkInternalCalc and fkAggregate fields can be used in filtering . I would not call myself a database guru, but I doubt that this statement is correct in general. The attached project uses a TBufDataset which has three predefined fields, IntField, Floatfield and TextField, plus a calculated field, IntFieldPlus1, which adds 1 to the IntField value. As you can see, filtering on the fkCalculated IntFieldPlus1 field works perfectly here. The next step would be to study a SQLite3 database in the same way, in both LCL SqlDB and in the ZEOS implementations. Unfortunately working with persistent fields in the IDE is quite a mess... I tested it and indeed it filters a calculated field wp I'm afraid I might be using another wrong method. Your observation is in place. It's good that you noticed that ---------------------------------------------------------------------- alpine · 2023-07-21 11:08 Quote from: Zvoni on July 21, 2023, 12:29:40 pmQuote from: alpine on July 21, 2023, 12:02:24 pmI don't want to complicate things further, but 'workday' is not the same as 'not weekend'. There are also official holidays, some of them not fixed at a date. Additional 'workday' calendar should be maintained separately. Be quiet!  Don't open another can of worms  Okay  But I feel like he's just getting introduced to the concept of date/time/DB handling. Besides the issues with LCL DB, Zeos, sqlite, etc. ---------------------------------------------------------------------- kito · 2023-07-21 11:11 Quote from: alpine on July 21, 2023, 12:02:24 pm I don't want to complicate things further, but 'workday' is not the same as 'not weekend'. There are also official holidays, some of them not fixed at a date. Additional 'workday' calendar should be maintained separately. Yes alpine      nice observation  and  also the starting day mustn't be  a weekend or holiday either that's why I  used master/detail  relationship  I made a table  OCA (each year must be manually updated ) : 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 CREATE TABLE "OCA" (        `ID`    INTEGER,        `DEF`   VARCHAR ( 50 ),      `ST`    VARCHAR ( 50 ),        PRIMARY KEY(`ID`) and I add every official holidays    (DEF:name of holiday/event ; ST: date   ) and I linked  VAC dataset with  OCA datasource linked field is  ST . and after editing done  it goes like that : 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 if  oca.recordcount <> 0 then  ED := ED+1 //  so recalculate DREP and for starting day the same thing : 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 procedure TForm1.DBGrid1PrepareCanvas(sender: TObject; DataCol: Integer;  Column: TColumn; AState: TGridDrawState);begin  if Column.FieldName='STC' then  begin         if  (DayOfWeek(  Column.Field.AsDateTime )=6 )or (DayOfWeek( Column.Field.AsDateTime )=7 ) or (oca.recoredcount <> 0 ) then     begin      with (Sender As TDBGrid) do      begin           Canvas.Brush.Color:=clYellow;        Canvas.Font.Color:=clRed;        Canvas.Font.Style:=[fsBold];      end;    end;end; end; ---------------------------------------------------------------------- Zvoni · 2023-07-21 11:24 Quote from: kito on July 21, 2023, 01:11:59 pmYes alpine      nice observation  and  also the starting day mustn't be  a weekend or holiday either that's why I  used master/detail  relationship  I made a table  OCA (each year must be manually updated ) : 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 CREATE TABLE "OCA" (        `ID`    INTEGER,        `DEF`   VARCHAR ( 50 ),      `ST`    VARCHAR ( 50 ),        PRIMARY KEY(`ID`) and I add every official holidays    (DEF:name of holiday/event ; ST: date   ) and I linked  VAC dataset with  OCA datasource linked field is  ST . Well, to stay in the Spirit of this: Could also easily be done in SQLite directly (untested, since it's been a long time i've done something like that): Introduce a new Column "Offset" (or whatever you like to call it), create two Triggers (After INSERT and After UPDATE), which checks if the calculated ED-Date falls on such a Holiday. If yes, Offset=1 else Offset=0 The generated columns just have to be adjusted with that Offset And now i'm going to introduce further complications: What about if there are two consecutive Holidays? Christmas-Day and Boxing-Day come to mind. Your offset ED:=ED+1 wouldn't work ---------------------------------------------------------------------- kito · 2023-07-21 11:25 Quote from: Zvoni on July 21, 2023, 12:29:40 pmQuote from: alpine on July 21, 2023, 12:02:24 pmI don't want to complicate things further, but 'workday' is not the same as 'not weekend'. There are also official holidays, some of them not fixed at a date. Additional 'workday' calendar should be maintained separately. Be quiet!  Don't open another can of worms     I am sure there are a lot  Quote from: alpine on July 21, 2023, 01:08:02 pmQuote from: Zvoni on July 21, 2023, 12:29:40 pmQuote from: alpine on July 21, 2023, 12:02:24 pmI don't want to complicate things further, but 'workday' is not the same as 'not weekend'. There are also official holidays, some of them not fixed at a date. Additional 'workday' calendar should be maintained separately. Be quiet!  Don't open another can of worms  Okay  But I feel like he's just getting introduced to the concept of date/time/DB handling. Besides the issues with LCL DB, Zeos, sqlite, etc. Indeed, in this discussion, I have corrected many misconceptions. Thank you very much guys, I am very grateful for your help and the way not neglecting any remarque ---------------------------------------------------------------------- kito · 2023-07-21 11:40 Quote from: Zvoni on July 21, 2023, 01:24:43 pm Well, to stay in the Spirit of this: Could also easily be done in SQLite directly (untested, since it's been a long time i've done something like that): Introduce a new Column "Offset" (or whatever you like to call it), create two Triggers (After INSERT and After UPDATE), which checks if the calculated ED-Date falls on such a Holiday. If yes, Offset=1 else Offset=0 The generated columns just have to be adjusted with that Offset Sounds great! a much way better Quote from: Zvoni on July 21, 2023, 01:24:43 pm And now i'm going to introduce further complications: What about if there are two consecutive Holidays? Christmas-Day and Boxing-Day come to mind. Your offset ED:=ED+1 wouldn't work what about : 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 while oca.recoredcount <> 0 do :begin ED := ED+1;dataset.applyupdates ; end ; ---------------------------------------------------------------------- alpine · 2023-07-21 12:17 Quote from: kito on July 21, 2023, 01:40:23 pm*snip* what about : 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 while oca.recoredcount <> 0 do :begin ED := ED+1;dataset.applyupdates ; end ;  "Careful with That Axe, Eugene" You're introducing an endless loop Better: ED:=ED+oca.recoredcount; but it is not correct either. Things can go very hairy pretty quickly. Enter the "time spans" abstraction. The idea is: When the end of the period falls into a holiday time-span (e.g. start..end or start..start+duration) then move it after the end. Thus you should introduce additional column to the holidays table, either end date or duration. And to replace linked table with a parametrized query. Also, when you edit the holidays table, care must be taken not to allow adjacent holiday spans, or to merge them in the query. ---------------------------------------------------------------------- kito · 2023-07-21 13:02 Quote from: alpine on July 21, 2023, 02:17:26 pmQuote from: kito on July 21, 2023, 01:40:23 pm*snip* what about : 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 while oca.recoredcount <> 0 do :begin ED := ED+1;dataset.applyupdates ; end ;  "Careful with That Axe, Eugene" You're introducing an endless loop Better: ED:=ED+oca.recoredcount; but it is not correct either. Things can go very hairy pretty quickly. Enter the "time spans" abstraction. The idea is: When the end of the period falls into a holiday time-span (e.g. start..end or start..start+duration) then move it after the end. Thus you should introduce additional column to the holidays table, either end date or duration. And to replace linked table with a parametrized query. Also, when you edit the holidays table, care must be taken not to allow adjacent holiday spans, or to merge them in the query. that's not even a can of warm I made a black hole ! ---------------------------------------------------------------------- Zvoni · 2023-07-21 13:09 Quote from: kito on July 21, 2023, 01:40:23 pmQuote from: Zvoni on July 21, 2023, 01:24:43 pm Well, to stay in the Spirit of this: Could also easily be done in SQLite directly (untested, since it's been a long time i've done something like that): Introduce a new Column "Offset" (or whatever you like to call it), create two Triggers (After INSERT and After UPDATE), which checks if the calculated ED-Date falls on such a Holiday. If yes, Offset=1 else Offset=0 The generated columns just have to be adjusted with that Offset Sounds great! a much way better Here we go: Important: You have to enable recursive Triggers!!! Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i CREATE TABLE "VAC2" (        "ID" INTEGER PRIMARY KEY,        "ST" TEXT,      "CO" INTEGER DEFAULT 0,        "OS" INTEGER DEFAULT 0,        "ED" TEXT GENERATED ALWAYS AS (DATE(JulianDay(ST)+CO-1)) STORED,        "DREP" TEXT GENERATED ALWAYS AS (                CASE strftime('%w',DATE(JulianDay(ED)+1+OS))                         WHEN '6' THEN DATE(JulianDay(ED)+1+OS,'weekday 0')                         WHEN '5' THEN DATE(JulianDay(ED)+1+OS,'weekday 0')                       ELSE DATE(JulianDay(ED)+1+OS)                         END) VIRTUAL);CREATE TABLE "OCA" (        "ID"    INTEGER PRIMARY KEY,      "DEF"   TEXT,        "ST"    TEXT);Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i CREATE TRIGGER "trgVACAftIns" AFTER INSERT ON "VAC2" FOR EACH ROWWHEN EXISTS (SELECT DATE(O.ST) FROM OCA AS O                          INNER JOIN VAC2 AS V ON V.ID=NEW.ID                         WHERE DATE(O.ST)=(CASE strftime('%w',DATE(JulianDay(NEW.ED)+1))                        WHEN '6' THEN DATE(JulianDay(NEW.ED)+1,'weekday 0')                        WHEN '5' THEN DATE(JulianDay(NEW.ED)+1,'weekday 0')                        ELSE DATE(JulianDay(NEW.ED)+1)                        END))        BEGIN                        UPDATE VAC2 SET OS=1 WHERE ID=NEW.ID;        END;CREATE TRIGGER "trgVACAftUpd" AFTER UPDATE ON "VAC2" FOR EACH ROWWHEN EXISTS (SELECT DATE(O.ST) FROM OCA AS O                          INNER JOIN VAC2 AS V ON V.ID=NEW.ID                       WHERE DATE(O.ST)=(CASE strftime('%w',DATE(JulianDay(NEW.ED)+1+NEW.OS))                        WHEN '6' THEN DATE(JulianDay(NEW.ED)+1+NEW.OS,'weekday 0')                        WHEN '5' THEN DATE(JulianDay(NEW.ED)+1+NEW.OS,'weekday 0')                          ELSE DATE(JulianDay(NEW.ED)+1+NEW.OS)                        END)                         )        BEGIN                      UPDATE VAC2 SET OS=OS+1 WHERE ID=NEW.ID;        END; Adding three holidays, with 2 consecutive holidays Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO OCA(DEF,ST) VALUES('Holiday1','2023-07-23');INSERT INTO OCA(DEF,ST) VALUES('Holiday2','2023-07-24');INSERT INTO OCA(DEF,ST) VALUES('Holiday3','2023-07-27'); INSERT INTO VAC2(ST,CO) VALUES('2023-07-01',18);INSERT INTO VAC2(ST,CO) VALUES('2023-07-02',18);INSERT INTO VAC2(ST,CO) VALUES('2023-07-03',18);INSERT INTO VAC2(ST,CO) VALUES('2023-07-04',18);INSERT INTO VAC2(ST,CO) VALUES('2023-07-05',18);INSERT INTO VAC2(ST,CO) VALUES('2023-07-06',18);INSERT INTO VAC2(ST,CO) VALUES('2023-07-07',18);INSERT INTO VAC2(ST,CO) VALUES('2023-07-08',18);INSERT INTO VAC2(ST,CO) VALUES('2023-07-09',18);INSERT INTO VAC2(ST,CO) VALUES('2023-07-10',18);INSERT INTO VAC2(ST,CO) VALUES('2023-07-11',18);INSERT INTO VAC2(ST,CO) VALUES('2023-07-12',18);INSERT INTO VAC2(ST,CO) VALUES('2023-07-13',18);INSERT INTO VAC2(ST,CO) VALUES('2023-07-14',18);INSERT INTO VAC2(ST,CO) VALUES('2023-07-15',18); Database attached Please check if DREP is correct ---------------------------------------------------------------------- Zvoni · 2023-07-21 14:05 Just noticed something to complicate it even further: Usually non working days are not counted for vacation days. Do you have to respect that? EDIT: just had an idea based on your holiday table to maybe slim everything down. Will have to think on it ---------------------------------------------------------------------- alpine · 2023-07-21 14:46 Quote from: Zvoni on July 21, 2023, 04:05:39 pmJust noticed something to complicate it even further: Usually non working days are not counted for vacation days. Do you have to respect that? Not to say that in some rare occasions the authorities may decide to extend a holiday (sole day remaining in the working week) in behalf of some later date. I have a table where any date can be overridden and I keep it resident in memory all the time. A function returns the type of the given day if it is listed as an exception, else simply checks the weekday number. ---------------------------------------------------------------------- kito · 2023-07-21 15:08 Quote from: Zvoni on July 21, 2023, 03:09:26 pm Please check if DREP is correct I am truly grateful for your time, efforts. when I try to connect to the database  via Zconnection (Zeos comp ) error message displays as :Error SQL : database disk image  is malformed 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 Usually non working days are not counted for vacation days.Do you have to respect that?Nope  but in the case of having a medical certificate, the vacation days during its validity are not counted, and the vacation must be extended according to the number of days in the certificate ---------------------------------------------------------------------- Zvoni · 2023-07-21 15:46 Quote from: kito on July 21, 2023, 05:08:41 pmQuote from: Zvoni on July 21, 2023, 03:09:26 pm Please check if DREP is correct I am truly grateful for your time, efforts. when I try to connect to the database  via Zconnection (Zeos comp ) error message displays as :Error SQL : database disk image  is malformedOUCH!! Which SQLite-Version do you have? As i said: It requires 3.31 or later Can anyone else confirm? Have you tried build a complte new Database with my SQL's above? Remember: You need to activate recursive Triggers in your Conncetion https://www.sqlite.org/pragma.html#pragma_recursive_triggers QuoteCode: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i Usually non working days are not counted for vacation days.Do you have to respect that?Nope  but in the case of having a medical certificate, the vacation days during its validity are not counted, and the vacation must be extended according to the number of days in the certificate Really? Weird. But not my Problem ---------------------------------------------------------------------- Zvoni · 2023-07-21 16:05 Right. Changed my approach. In a nutshell: i treat friday and saturday like any other "hard" Holiday, a.k.a "non working day" I created a View "NonWorkingDays", consisting of a FULL OUTER JOIN of a Calendar CTE only grabbing fridays and saturdays and the holidays in OCA, with Holidays taking precedence (a holiday can be on a friday or saturday). The "Range" of the Calendar CTE is StartOfYear of today ('2023-01-01') to Today + 2 Years ('2025-07-21') The View can be change to be more "dynamic", e.g. Today -1 year to Today + 2 Years ('2022-07-22' to '2025-07-21'). In that case "Old" View must be dropped and recreated a new Field DREP in VAC2 has also been changed incl. both Triggers for VAC2 Basically: If ED+1 hits on one of those days, the Offset OS gets increased by 1, and DREP bascially boils down to EP+1+OS ---------------------------------------------------------------------- kito · 2023-07-21 16:47 Quote OUCH!! Which SQLite-Version do you have? As i said: It requires 3.31 or later Can anyone else confirm? Have you tried build a complte new Database with my SQL's above? Remember: You need to activate recursive Triggers in your Conncetion https://www.sqlite.org/pragma.html#pragma_recursive_triggers sorry my bad I was using an older version of SQLite. after updating the lib  database is connected the fields are displayed drep is so correct none a single mistake! but I can edit nothing QuoteRemember: You need to activate recursive Triggers in your connection https://www.sqlite.org/pragma.html#pragma_recursive_triggers I don't really know how exactly do that I add in Tzconnection the following property 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 PRAGMA recursive_triggers = on; and also after the database is connected : 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.ZConnection1.ExecuteDirect('PRAGMA recursive_triggers = on;'); ---------------------------------------------------------------------- Zvoni · 2023-07-21 17:05 The PRAGMA execution is correct Note: in my approach you can’t edit ED or DREP, Only ST, CO and OS. Every calculation is done by the database. Why would you want to EDIT that? Note: there is a way you can edit ED and DREP, but it involves a bit of redesign and tweaking. The main purpose of my approach was to show you, that you don’t need that convoluted calculation in your pascal code ---------------------------------------------------------------------- kito · 2023-07-21 17:18 Quote from: Zvoni on July 21, 2023, 07:05:05 pmThe PRAGMA execution is correct Note: in my approach you can’t edit ED or DREP, Only ST, CO and OS. Every calculation is done by the database. Why would you want to EDIT that? I meant  that The triggers wont executed instantly after editing Co,ST unless I reopen the program ---------------------------------------------------------------------- Zvoni · 2023-07-21 17:21 Did you refresh everything? ---------------------------------------------------------------------- kito · 2023-07-21 17:29 Quote from: Zvoni on July 21, 2023, 07:21:17 pmDid you refresh everything? you are my hero! Everything worked smoothly and correctly. Now, I can get rid of the visual clutter I created in my program ---------------------------------------------------------------------- alpine · 2023-07-22 10:42 @Zvoni There is an issue with the trgVACAftUpd trigger. It only increments the VAC2.OS and if you update VAC2.ST or VAC2.CO several times then VAC2.OS ends up with incorrect value. Instead of using that clever recursive increase, I would propose the following triggers (using recursive CTE as alternative): Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i CREATE TRIGGER tVACai         AFTER INSERT            ON VAC2      FOR EACH ROWBEGIN    UPDATE VAC2       SET OS = (           WITH nwd (st)             AS (                   SELECT O.ST                     FROM vwNonWorkingDays AS O                     WHERE DATE(O.ST) = DATE(JulianDay(NEW.ED) + 1)                    UNION ALL                 SELECT O.ST                     FROM vwNonWorkingDays AS O                        INNER JOIN                          nwd ON DATE(O.ST) = DATE(JulianDay(nwd.st) + 1)                )               SELECT COALESCE(JulianDay(MAX(st) ) - JulianDay(NEW.ED), 0)                  FROM nwd           )     WHERE ID = NEW.ID;END; Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i CREATE TRIGGER tVACau         AFTER UPDATE            ON VAC2      FOR EACH ROW          WHEN OLD.ED <> NEW.EDBEGIN    UPDATE VAC2       SET OS = (           WITH nwd (st)               AS (                   SELECT O.ST                     FROM vwNonWorkingDays AS O                   WHERE DATE(O.ST) = DATE(JulianDay(NEW.ED) + 1)                    UNION ALL                   SELECT O.ST                     FROM vwNonWorkingDays AS O                          INNER JOIN                        nwd ON DATE(O.ST) = DATE(JulianDay(nwd.st) + 1)              )               SELECT COALESCE(JulianDay(MAX(st) ) - JulianDay(NEW.ED), 0)                  FROM nwd           )     WHERE ID = NEW.ID;END; Computing OS in triggers raises the issue what if the holiday table changed in a way that affects the existing rows in VAC2. ---------------------------------------------------------------------- Zvoni · 2023-07-22 10:48 Interesting. Didn’t think about that. Nicely done ---------------------------------------------------------------------- alpine · 2023-07-22 11:36 QuoteComputing OS in triggers raises the issue what if the holiday table changed in a way that affects the existing rows in VAC2. Still my opinion is that these should be calculated at the time of reading, i.e. not stored, to reflect the current data into the holiday table. A covering VIEW can be used to extend the VAC2 table with the calculated ED, DPREP. Pity is the VIEW is read-only in sqlite and it will require INSTEAD OF triggers to update the underlying data. ---------------------------------------------------------------------- Zvoni · 2023-07-22 13:09 True, but i had to use STORED for ED to make it available for the New qualifier, otherwise i would have to implement the whole calculation for ED