Forum > Beginners

Filtering SQLite database by date

<< < (2/2)

What Frank said.
If you use "Text"-Affinity for the Database-Field, use ISO-Format.
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

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...


--- 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";}};} ---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; function ISODate(const ADate: TDateTime): string;begin  Result := FormatDateTime('yyyy-mm-dd', ADate);end; function ISODate(const DateString: string; ReturnUTC: Boolean): TDateTime;begin  Result := DateOf(ISODateTime(DateString, ReturnUTC));end; function ISOTime(const ADate: TDateTime): string;begin  Result := FormatDateTime('hh:mm:ss', ADate);end; function ISOTime(const DateString: string; ReturnUTC: Boolean): TDateTime;begin  Result := TimeOf(ISODateTime(DateString, ReturnUTC));end;

Thanks, that's handy! :D


[0] Message Index

[*] Previous page

Go to full version