Getting results from sql statement with between two dates

[CraigC]

Well, I am now down to "LazEditTextDridPainter" used by SynGutterBase.  SynEdit is installed as far as I can tell.

Thank you for taking the time to put this project together.

[paweld]

The version without Synedit is attached.

[CraigC]

It works.   But, I don't understand how it works because I have formatted the dates the same way.  yyyy-mm-dd  and mm-dd-yyyy neither one worked.

I'm going to step through this tomorrow and see what is done differently.  Thank you for your time.

Craig

[Zvoni]

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

1.   with qryProject_ do
2.   begin
3.     Active := False;
4.     SQL.Clear;
5.     SQL.Text := 'SELECT * FROM TODO WHERE ';
6.     case rgFilterOptions.ItemIndex of
7.       0 : SQL.Add('TODO.DateCompleted <> null ');
8.       1 : SQL.Add('TODO.DateCompleted = NULL ');
9.     end;
10.     SQL.Add('AND TODO.SECTION = :sSec ');
11.     SQL.Add('AND TODO.DateCreated BETWEEN :sStart AND TODO.DateCreated <= :sEnd');
12.     SQL.Add(' ORDER BY TODO.DateCreated');
13.     Params.ParamByName('sSec').AsString := qrySection_Section.AsString;
14.     Params.ParamByName('sStart').AsString := FormatDateTime('yyyy-mm-dd', dtpStart.Date);
15.     Params.ParamByName('sEnd').AsString := FormatDateTime('yyyy-mm-dd',dtpEnd.Date);
16.     Active := True;
17.   End;
18. End;
19.  

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

1. CREATE TABLE ToDo
2. (
3.     UID           INTEGER NOT NULL,
4.     Subject       CHAR(20),
5.     SECTION       CHAR(10),
6.     Details       BLOB,
7.     FileName      CHAR(20),
8.     DateCreated   CHAR(10) DEFAULT NULL,
9.     DateCompleted CHAR(10) DEFAULT NULL,
10.     CONSTRAINT PK_ToDo PRIMARY KEY (UID)

Right, as already said in your other thread (and mentioned by gsa)

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

1. case rgFilterOptions.ItemIndex of
2.       0 : SQL.Add('TODO.DateCompleted <> null ');
3.       1 : SQL.Add('TODO.DateCompleted = NULL ');

You cannot compare/filter directly on NULL. Period!
This never returns records.
gsa showed the correct syntax

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

1. SQL.Add('AND TODO.DateCreated BETWEEN :sStart AND TODO.DateCreated <= :sEnd');

This is wrong. You're looking for DateCreated between :sStart and a Boolean, because SQL interprets this as a Boolean: TODO.DateCreated <= :sEnd
pawel already showed you the correct syntax.
I'm more surprised this doesn't throw a syntax-error

I'm using Date-Fields in SQLite extensively, and never had a problem, provided you USE the correct format in the Database itself, which should always be "YYYY-MM-DD" as Text/Char and nothing else.

On a sidenote: If you use the BETWEEN-Operator in your WHERE-Clause, the LEFT Parameter MUST be lower/equal than the RIGHT Parameter
A ".... MyDate BETWEEN '2026-06-16' AND '2026-03-13' will never return records, even if you have records falling into that time-window

EDIT: hmmm...maybe this is actually what happened:

I'm more surprised this doesn't throw a syntax-error

The right-hand-side of the BETWEEN is evaluated as Boolean, which returns "0" or "1" as an Integer.
The left-hand-side receives a valid value for sStart, which is >0/1, or worse: left hand side is a String, right hand-side is a boolean/Integer
When we account for implicit casting of right-hand-side from boolean/Integer to String...
and then we would have the above scenario:
left-parameter Greater than right parameter --> No results

EDIT2: Remember: BETWEEN is inclusive of the boundaries.
If you need even one boundary excluded, you can't use BETWEEN, but you need to write it out

[CraigC]

Thank you for the reply.  I have tried so many different variations it's not funny.    The date format, I tried both. I'm sure my changing the SQL statement so many times probably got me in trouble along the way. 

Thank you both for your time and expertise.

[Zvoni]

Thank you for the reply.  I have tried so many different variations it's not funny.    The date format, I tried both. I'm sure my changing the SQL statement so many times probably got me in trouble along the way. 

Thank you both for your time and expertise.

There are 3 ways to store dates in SQLite
1) TEXT in ISO8601-Format
2) JulianDay --> Real (Float/Double)
3) Unixepoch (Integer)

After many try-and-error i've come to the conclusion, that the TEXT-Format is the "best" way regarding the ratio of "upside/downside"
and definitely no mixing of Formats:
ISO8601-Format. Period!
"YYYY-MM-DD" (or "YYYY-MM-DD HH:NN:SS.ZZZ" if you additionally need the time of day)
and nothing else

[CraigC]

The date is being stored as "mm-dd-yyyy" in a date field.  So, it looks like I really need a text field and store the date in "yyyy-mm-dd" ?

[cdbc]

Hi

So, it looks like I really need a text field and store the date in "yyyy-mm-dd" ?

YES!
Regards Benny

[Zvoni]

The date is being stored as "mm-dd-yyyy" in a date field.  So, it looks like I really need a text field and store the date in "yyyy-mm-dd" ?

Don't use "Date" as DataType for a Column. It get's numeric affinity
Use CHAR/VARCHAR/TEXT (as shown in your first post), and store the dates as "YYYY-MM-DD" and nothing else

Example

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

1. -- all Dates in Format "mm-dd-yyyy"
2. WITH
3.         V       AS (SELECT '08-18-2024' AS FromDate, '02-26-2026' AS ToDate),
4.         X       AS (SELECT '06-16-2025' AS MyDate)
5.        
6. SELECT *
7. FROM X
8. INNER JOIN V ON 1=1
9. WHERE MyDate BETWEEN FromDate AND ToDate

Returns no record, despite "June, 16th 2025" being between the 2 Dates

OTOH

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

1. -- all dates in Format "yyyy-mm-dd"
2. WITH
3.         V       AS (SELECT '2024-08-18' AS FromDate, '2026-02-26' AS ToDate),
4.         X       AS (SELECT '2025-06-16' AS MyDate)
5.        
6. SELECT *
7. FROM X
8. INNER JOIN V ON 1=1
9. WHERE MyDate BETWEEN FromDate AND ToDate

Returns the correct record

[CraigC]

Looks like my region defaults to "mm-dd-yyyy"  and if I change display format and DateOrder it still wants to save as "mm-dd-yyyy"  I did a showmessage before post event.  So I should use a parameter for INSERT and UPDATE SQL statements? I tried adding a @s to the fieldname.  that doesn't work.

It's getting more complicated by the moment.  Not even sure what event to add the setting of the params.

procedure TfrmProject.qryProject_BeforeOpen(Dataset: Tdataset);
begin
  with qryProject_ do
  begin
    SQL.Text := 'SELECT UID, DateCreated, Subject, Section, Details, DateCompleted, FileName '
      + ' FROM TODO Order BY DateCreated ';
    InsertSQL.Text :=
      'INSERT INTO ToDo(DateCreated, Subject, Section, Details, DateCompleted, FileName) ' +
      'VALUES(:@sDateCreated, :Subject, :Section, :Details, :@sDateCompleted, :FileName)';
    UpdateSQL.Text :=
      'UPDATE ToDo SET DateCreated=:@sDateCreated, Subject=:Subject, Section=:Section, ' +
      'Details=:Details, DateCompleted=:@sDateCompleted, FileName=:FileName WHERE UID=:OLD_UID';
  end;
end;

Procedure Tfrmproject.Qryproject_beforepost(Dataset : Tdataset);
Begin
  qryProject_.Params.ParamByName('@sDateCreated').AsString := FormatDateTime('yyyy-mm-dd', Qryproject_datecreated.AsDateTime);
  if not Qryproject_datecompleted.IsNull then
  qryProject_.Params.ParamByName('@sDateCompleted').AsString := FormatDateTime('yyyy-mm-dd', Qryproject_datecompleted.AsDateTime);
End;

[Zvoni]

Remove the "@" from the Parameter-Names.
It's either a colon ":", or it's the "@", never both.

And you should remember, there is a difference, how something is displayed (--> Locales!), and how something is stored.

Write a simple routine taking a TDateTime (e.g. from a Datepicker),
and fire an INSERT against your Database, using the correct Formatting (as shown in your sample above).
and then look in the Database itself, if it's stored correctly, because SQLite is locale-agnostic regarding Date-Formats

EDIT: Just noticed: You are using InsertSQL and UpdateSQL
Parameternames MUST be the Columnname prepended with a colon ":"
Remove the "@" and "s"

UpdateSQL.Text :=
      'UPDATE ToDo SET DateCreated=:@sDateCreated, Subject=:Subject, Section=:Section, ' +
      'Details=:Details, DateCompleted=:@sDateCompleted, FileName=:FileName WHERE UID=:OLD_UID';

[CraigC]

I have the date being saved correctly. See screenshot. But the filter for only the selected section and dates, does not work, (again) I don't see a reason why.

 with qryProject_ do
  begin
    Active := False;
    SQL.Clear;
    SQL.Text := 'SELECT UID, DateCreated, Subject, Section, Details, DateCompleted, FileName '
      + ' FROM TODO WHERE TODO.SECTION = :sSec AND DateCreated BETWEEN :sStart AND :sEnd ' +
      ' ORDER BY TODO.DateCreated ';
    Params.ParamByName('sSec').AsString := qrySection_Section.AsString;
    Params.ParamByName('sStart').AsString := FormatDateTime('yyyy-mm-dd', dtpStart.Date);
    Params.ParamByName('sEnd').AsString := FormatDateTime('yyyy-mm-dd', dtpEnd.Date);
    mSQL.Clear;
    mSQL.Lines.Add(qrySection_Section.AsString);
    mSQL.Lines.Add(params.ParamByName('sStart').AsString);
    mSQL.Lines.Add(params.ParamByName('sEnd').AsString);
    mSQL.Lines.Add(qryProject_.SQL.Text);
    Open;
  end;

[eny]

I'm assuming that 'Completed' is your dtEnd.
As mentioned above, comparing any value with a NULL value will not give any results.
This also applies to Between.

You have to handle the possible NULL values in dtEnd explicitly by, for example, using the COALESCE function.
So change your query to something like below:

Code: [Select]

SELECT UID, DateCreated, Subject, Section, Details, DateCompleted, FileName
  FROM TODO
WHERE TODO.SECTION = :sSec
   AND DateCreated BETWEEN :sStart AND COALESCE(:sEnd, '9999-12-31')
 ORDER BY TODO.DateCreated


[CraigC]

Completed is the date in which the task was completed.  It's not used in the parameters. Just the DateCreated.

Completed will be null for the most part.

[eny]

Can dtEnd be NULL?
Is it NULL in your (test) data?