Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: SQLite SQL for dates


This question is not answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 3 - Last Post: Feb 3, 2015 1:12 AM Last Post By: Dmitry Arefiev
Larry X

Posts: 15
Registered: 6/29/06
SQLite SQL for dates  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 31, 2015 12:09 PM
If the database's setting is DateTimeFormat=binary, then a query such as: Select * from ATableName where ADate > JulianDay("2014-01-01") works absolutely fine.

But if the database's setting is DateTimeFormat=DateTime, how do I modify the SQL query above? Do I have to write a custom function instead of using SQLite's built in JulianDay function?

Thank you,

Larry
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: SQLite SQL for dates  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 2, 2015 4:47 AM   in response to: Larry X in response to: Larry X
But if the database's setting is DateTimeFormat=DateTime, how do I modify the SQL query above? Do I have to write a custom function instead of using SQLite's built in JulianDay function?

DateTimeFormat=DateTime is made more for compatibility with other DAC's,
which are storing date/time values using Delphi native TDateTime format.
SQLite itself does not support this format. So, yes - you will need to
write your own custom functions.

--
With best regards,
Dmitry Arefiev / FireDAC Architect
Larry X

Posts: 15
Registered: 6/29/06
Re: SQLite SQL for dates  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 2, 2015 5:58 PM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Thanks! I got the custom function to work well within an SQL query (I called it the DelphiDay function). How about handing dates within a filter? what's the approach there? Larry X
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: SQLite SQL for dates  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 3, 2015 1:12 AM   in response to: Larry X in response to: Larry X
How about handing dates within a filter? what's the approach there? Larry X

DateTimeFormat parameter affects only on SQLite database storage format.
It does not affect on the Delphi side TDataSet descendants. So, you can
use standard filter expressions. Eg,
FDQuery1.Filter := 'dateField = ''date in your local dateformat''';

--
With best regards,
Dmitry Arefiev / FireDAC Architect
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02