• Registered: 2015-10-12
  • Posts: 84

Topic: Sql statement to display records based on a date field

Hi

I have an sqlite table that contains a date field with values like : '22/11/2014 2:16:37 μμ'       (μμ stands for pm in greek)

I have a display grid that shows all the records from this table.

I want to be able to select a date period in months e.g. 24 and create an sql search statement that will display all records whose date is less than 24 months from now.

The table contains products in a warehouse  and the date field corresponds to the product's storage date. I want to find out how many products are older that X days in storage from now.

For example: Form1.Tablegrid1.dbSQL:='SELECT * FROM "'+product_table+'" WHERE "Ημερομηνίαπροσθήκης" < "'+dateSearchString+'" ORDER BY "Feature:Μάρκα","Feature:Μοντέλο","Feature:;Έτος","Feature:ΕίδοςΑνταλλακτικού"';

This query works only for the '=' operator. When I use '>' or'<' it does not work correctly and returns wrong results.

Thank you

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 891

Re: Sql statement to display records based on a date field

Hi


In order to help you, you need to see your project.

  • k245
  • k245
  • Member
  • Offline
  • From: Deep
  • Registered: 2017-10-12
  • Posts: 1,532

Re: Sql statement to display records based on a date field

Please specify which DBMS are you using? You need conversion functions to work with dates. For SQLite, these are the SQL functions date() - converts a string representation to a date, or julianday() - converts a string representation to a number of days.

Визуальное программирование: блог и телеграм-канал.

k245's Website

  • Registered: 2015-10-12
  • Posts: 84

Re: Sql statement to display records based on a date field

Unfortunately the project is too big to upload. I can upload some pictures in a word file.

Under the grid there is a field that I can input a date and next to it is a button to run the query to display all records that their storage date  is older than
the date selected.

Thank you

Post's attachments

Attachment icon mvd.docx 205.67 kb, 190 downloads since 2022-11-01 

5 Reply by geochrist (edited by geochrist 2022-11-01 11:18:13)

  • Registered: 2015-10-12
  • Posts: 84

Re: Sql statement to display records based on a date field

As can be seen I get a result when I use the '=' operator.
The results are displayed in the last word page.
If I change the operator to '<' it does not work correctly.

Thank you again.

  • Registered: 2015-10-12
  • Posts: 84

Re: Sql statement to display records based on a date field

I could also send the project to you via wetransfer.

You could post the answer on the forum.

Thank you

7 Reply by geochrist (edited by geochrist 2022-11-01 11:40:55)

  • Registered: 2015-10-12
  • Posts: 84

Re: Sql statement to display records based on a date field

I am using sqlite.

In the above example the string variable dateSearchString contains a value like this: 21/11/2014 6:09:41 μμ

How can I use the date() function in the sql satetement mentioned in the first post. Can you write an example?

The date values in the sqlite database table have the same format like this 21/11/2014 6:09:41 μμ

Thank you

  • k245
  • k245
  • Member
  • Offline
  • From: Deep
  • Registered: 2017-10-12
  • Posts: 1,532

Re: Sql statement to display records based on a date field

geochrist wrote:

I am using sqlite.

In the above example the string variable dateSearchString contains a value like this: 21/11/2014 6:09:41 μμ

How can I use the date() function in the sql satetement mentioned in the first post. Can you write an example?

The date values in the sqlite database table have the same format like this 21/11/2014 6:09:41 μμ

Thank you

Form1.Tablegrid1.dbSQL := 'SELECT * FROM table WHERE date( DateField ) < date( "'+dateSearchString+'" ) ORDER BY 1';

Are you sure that the date data is stored in the database in the format you specified? Usually there is something like this:

2022-11-01 14:52:34.543

YYYY-MM-DD HH:MM:SS.SSS

Визуальное программирование: блог и телеграм-канал.

k245's Website

  • Registered: 2015-10-12
  • Posts: 84

Re: Sql statement to display records based on a date field

Please take a look at the attached file. It shows the data in the database.

Post's attachments

Attachment icon sqliteDB.png 52.54 kb, 69 downloads since 2022-11-01 

10 Reply by geochrist (edited by geochrist 2022-11-01 12:05:23)

  • Registered: 2015-10-12
  • Posts: 84

Re: Sql statement to display records based on a date field

I tried your solution but I do not get any results.

The data in the sqlite database have been imported from a csv file.
Maybe I have to convert them to the format you mentioned above.

11 Reply by k245 (edited by k245 2022-11-01 12:08:02)

  • k245
  • k245
  • Member
  • Offline
  • From: Deep
  • Registered: 2017-10-12
  • Posts: 1,532

Re: Sql statement to display records based on a date field

This is the date/time display format that is accepted for your region, so the db browser displays the date according to the selected locale. Physically, the data is stored in the format that I wrote above.


Enter a simple SQL query to see how the data is actually stored:

SELECT  Ημερομηνίαπροσθήκης FROM products

Alternatively, it's just a text field, not a date/time field. I don't see it in your screenshot.

Визуальное программирование: блог и телеграм-канал.

k245's Website

  • Registered: 2015-10-12
  • Posts: 84

Re: Sql statement to display records based on a date field

I think I understand now.
The data that is stored in the sqlite database in the field named 'Ημερομηνίαπροσθήκης' are just text strings. They are not dates in a date field. They are text in a string field. That is why only the '=' operator works.

I will try to figure this out.
Many thanks for your precious help.

13 Reply by sparrow (edited by sparrow 2022-11-01 13:18:17)

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 891

Re: Sql statement to display records based on a date field

I'm not sure you understand

here is an example

SELECT * FROM table WHERE DATE(datecolumn) > DATE('now', '-24 month')

OR

SELECT * FROM table  WHERE DATE(datecolumn) >= DATE('now', '-8 month') AND DATE(datecolumn) <= DATE('now', '-6 month')

OR 

SELECT * FROM table  WHERE DATE(datecolumn) BETWEEN DATE('now', '-8 month') AND DATE('now', '-6 month')

in order to attach the project, you need to delete the executable file (*.EXE) and archive it. That would make it much easier to give you an answer.


Date ranges are given as an example. Use whatever you need. And the SQLite documentation - https://www.sqlite.org/lang_datefunc.html