SQlite returning empty array on date range

Hi to all, sorry if this question has been raised but I could find answer to my problem.
I have a data base which has the following format:

My problem is as follows:
I have created separate graphs which will display daily, weekly and monthly sales figures. The graphing is fine but the main issue I am having at times is retrieving data from the database with using the a date range as the selection criteria.

I am using the following code to retrieve data (monthly example):

Now this works well when (example , start date:01/04/2020 and end date:30/04/2020):
The date stipulate is found within the database from start to finish.

It does not work well when (example; start date: 10/04/2020 and end date: 09/05/2020 )
if either the start date or end date is not found withing the database. So current date is 09/05/2020 and there are no records in the 05 month but there are records in the 04 month. basically there is data in that range but the database has no entry for the 05 month.

I am abit confused as the code I'm using is from the start date or later until the end date or earlier (>= start date and <= end date). With my little understanding that the above shouldn't be a problem if at least one value is found within that date range. That however is not the case and it returns an empty array even though I can see that database contains some entries within that specific range.

Your assistance would be much appreciated

the above image shows the debug node as well as the date range going into the function. The data base clearly shows entries within the stipulated date range but I still receive an empty array.

What is the type of the date and time fields in the database? If they are TEXT then that could be your issue.

1 Like

Hi Zenomud
Sorry for my late reply and thanks for pointing that out.

I checked the data base and it is being stored as an INT, which could be the problem as the date is in a string format.

If I were to change the types to Date and Time respectively would I still receive the same error if saved in the above format(dd/mm/yyyy)?

I'm assuming that using a the standard unix timestamp in the database would work out better and just convert to the above format for display purposes?

kind Regards

You might want to read up on sqlite data types https://www.sqlite.org/datatype3.html

Thanks will give it a read and see what i can come up with

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.