Filtering SQLite database by date

[heebiejeebies]

Hi all,

I need to filter an SQLite database by date.  Database currently has a date column with the date recorded in the format DD/MM/YYYY, although this is user selectable to be MM/DD/YYYY.

I set up a TDateTimePicker to act as the filter control, but it has a couple of problems.  Firstly, its date value omits the leading 20 from the year, which means the format doesn't precisely match the date column, so the SQL query fails to find any entries.  Secondly, if one user has their system set to DD/MM/YYYY and another has theirs set to MM/DD/YYYY, we'll have a similar problem if they share database files.  Any idea how to approach this?  I'm thinking of perhaps adding a hidden column that stores the Julian date, although I can't even seem to figure out how to do that.  Any ideas?

Query for my DBGrid is:

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

1. SQLQuery1.Close;
2. SQLQuery1.SQL.Text :=('SELECT * FROM HANDOFFS WHERE DATE =:DATEPARAM');
3. SQLQuery1.Params.ParamByName('DateParam).Value := DateToStr(DateTimePicker1.Date);
4. SQLQuery1.Open;      
5.  

This works fine if I change the value of the DateParam to a different source in the format of DD/MM/YYYY.

[Thausand]

documentation https://www.freepascal.org/docs-html/rtl/sysutils/datetostr.html write:

Converts a TDateTime value to a date string with a predefined format.

You not want that.

better:
- https://www.freepascal.org/docs-html/rtl/sysutils/formatdatetime.html
- https://www.freepascal.org/docs-html/rtl/sysutils/formatchars.html

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

1. program test;
2.  
3. uses
4.   sysutils;
5.  
6. type
7.   TDBDateType=(dbdtDDMMYYYY, dbdtDDYYYYMM);
8. var
9.   today:TDateTime;
10.   value:string;
11.   whichDBDateType:TDBDateType;
12. begin
13.   today:=Date;
14.  
15.   // how you know ? is store in DB ?
16.   whichDBDateType:=dbdtDDMMYYYY;
17.  
18.   case whichDbDateType of
19.    dbdtDDMMYYYY:value:=FormatDateTime('DD"/"MM"/"YYYY',today);
20.    dbdtDDYYYYMM:value:=FormatDateTime('DD"/"YYYY"/"MM',today);
21.    else value:='not know DB date type'
22.   end;
23.  
24.   writeln('today = ',value);
25. end.
26.  

But how know DB store date type ? is bad to have different date type.

[dseligo]

Do it like this:

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

1. SQLQuery1.Close;
2. SQLQuery1.SQL.Text := 'SELECT * FROM HANDOFFS WHERE DATE =:DATEPARAM';
3. SQLQuery1.ParamByName('DATEPARAM').AsDate := DateTimePicker1.Date;
4. SQLQuery1.Open;      
5.  

If dates in database are stored as dates, you don't format date when searching. You provide your query with TDateTime type value.

[af0815]

User selectable in the DB is a can of worms. The user only should select the reprentation of the Datetime in the GUI. In the DB only dates as deseigo says. In other Server DBs you can only design a precision of the datetime value. Never the internal format.
Never deal with strings if you work with dates. Only use the Datetime parameter.
 

[SymbolicFrank]

Do all dates share the same timezone? Do they all omit the time?
SQLite has no date fields. You can store your dates as doubles (TDateTime), integers (Unix time) or strings (ISO). To find the day, you truncate the double, check the integer for a range (00:00:00 .. 23:59:59) or the string for a pattern (0000-00-00).

[Zvoni]

What Frank said.
If you use "Text"-Affinity for the Database-Field, use ISO-Format.
Period!
If you want to evaluate any other "Text"-Representation of Dates it must be done by the Frontend.
Using any other Text-Representation than ISO-Format looses you any and all functionality of SQL within SQLite itself (ORDER BY for example)

EDIT: And don't use "Date" as a column-name

[heebiejeebies]

Thanks guys.  I just created another invisible date field in the database that stores the date in a set format, and a visible one that stores the date in the user's preferred format.  Surprisingly, even if you change the displayed date order of the TDateTimePicker, it doesn't seem to change the format of the actual date value of the picker, so it still matches.

I feel like I'm going to get growled at for something in this, but so far it works...

[SymbolicFrank]

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

1. function ISODateTime(const ADate: TDateTime; AddT: Boolean = False): string;
2. begin
3.   if AddT then
4.     Result := FormatDateTime('yyyy-mm-dd"T"hh:mm:ss', ADate)
5.   else
6.     Result := FormatDateTime('yyyy-mm-dd hh:mm:ss', ADate);
7. end;
8.  
9. function ISODateTime(const DateString: string; ReturnUTC : Boolean = False):
10.   TDateTime;
11. begin
12.   TryISO8601ToDate(DateString, Result, ReturnUTC);
13. end;
14.  
15. function ISODate(const ADate: TDateTime): string;
16. begin
17.   Result := FormatDateTime('yyyy-mm-dd', ADate);
18. end;
19.  
20. function ISODate(const DateString: string; ReturnUTC: Boolean): TDateTime;
21. begin
22.   Result := DateOf(ISODateTime(DateString, ReturnUTC));
23. end;
24.  
25. function ISOTime(const ADate: TDateTime): string;
26. begin
27.   Result := FormatDateTime('hh:mm:ss', ADate);
28. end;
29.  
30. function ISOTime(const DateString: string; ReturnUTC: Boolean): TDateTime;
31. begin
32.   Result := TimeOf(ISODateTime(DateString, ReturnUTC));
33. end;

[heebiejeebies]

Thanks, that's handy!