Forum > Beginners
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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---SQLQuery1.Close;SQLQuery1.SQL.Text :=('SELECT * FROM HANDOFFS WHERE DATE =:DATEPARAM');SQLQuery1.Params.ParamByName('DateParam).Value := DateToStr(DateTimePicker1.Date);SQLQuery1.Open;
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:
--- Quote ---Converts a TDateTime value to a date string with a predefined format.
--- End quote ---
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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---program test; uses sysutils; type TDBDateType=(dbdtDDMMYYYY, dbdtDDYYYYMM);var today:TDateTime; value:string; whichDBDateType:TDBDateType;begin today:=Date; // how you know ? is store in DB ? whichDBDateType:=dbdtDDMMYYYY; case whichDbDateType of dbdtDDMMYYYY:value:=FormatDateTime('DD"/"MM"/"YYYY',today); dbdtDDYYYYMM:value:=FormatDateTime('DD"/"YYYY"/"MM',today); else value:='not know DB date type' end; writeln('today = ',value);end.
But how know DB store date type ? is bad to have different date type.
dseligo:
Do it like this:
--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---SQLQuery1.Close;SQLQuery1.SQL.Text := 'SELECT * FROM HANDOFFS WHERE DATE =:DATEPARAM';SQLQuery1.ParamByName('DATEPARAM').AsDate := DateTimePicker1.Date;SQLQuery1.Open;
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).
Navigation
[0] Message Index
[#] Next page