Android Question SQL Date Selection

[Pelky]

Good Day All.... I have a problem regarding selecting records from an SQL database using dates. The program worked until I came into 2024 and the system no longer selects records.

        Dim TotalsCursor As ResultSet
'        TotalsCursor = DB.ExecQuery2("SELECT * FROM LogRecord WHERE  LogDate BETWEEN ? AND ?", Array As String(FromArray(0), ToArray(0)))
        TotalsCursor = DB.ExecQuery2("SELECT * FROM LogRecord WHERE  LogDate BETWEEN ? AND ?", Array As String(DateTime.DateParse(FromArray(0)), DateTime.DateParse(ToArray(0))))

        Log("From Date "&FromArray(0)&"  to "&ToArray(0)&" ***  records found "&TotalsCursor.RowCount)
        Log("Real from = "&DateTime.DateParse(FromArray(0))&" To "&DateTime.DateParse(ToArray(0)))

I have commented out the original selection and changed it to use DateParse as the logs show the dates correctly and parsed dates so I wanted to see if using dateparse in the selection would help - no difference.

The 'records found '0' should read '15' - those records are not being found. As can be seen from the log (below) - last years (for month) records are found and this problem only came about at the change of year

Pay Day 08/26/2023
Month 7 Year 2023
Pay Day 07/29/2023
Pay Date should be 12/30/2023 December to 01/27/2024 January
From Date 12/30/2023 to 01/27/2024 *** records found 0
Real from = 1703894400000 To 1706313600000
Result2 00:00
Result3 00:00
Result4 00:00
Result7 00:00
Pay Date should be 11/25/2023 November to 12/30/2023 December
From Date 11/25/2023 to 12/30/2023 *** records found 36
Real from = 1700870400000 To 1703894400000
Result2 262:00
Result3 62:00
Result4 88:05
Result7 08:00
Pay Date should be 10/28/2023 October to 11/25/2023 November


I am at a loss as to what could be the problem as the log shows the From/To appear correct.

As always I respect and appreciate any assistance or ideas that you may have

Regards
Malcolm

 

[MicroDrie]

The result could be a result of both the date setting of the server and that of the client. Ultimately you come to the question of how is the date stored in the SQL database? Was there a textual format change? If yes then you will have to change the query to make the solution to one standard result before you make the selection into the format you use last year.

See also SQLite query date format or a broader search on SQL date

 

[Pelky]

Thank you for your response- it is much appreciated.

The application had not been modified since october of 2023 and only stopped showing results in January of this year. It is a standalone app so no server involvement as I understand it. Because it is using dir internal I will have to write a section to show mevwhat is actually in the DB... painful..

Anyway I would like to once again like to thank you for your response..

 

[aeric]

Are you storing LogDate as Integer in SQLite?

 

[Pelky]

Hello Aeric...
I made a big mistake and did not store the logdate as a long - basically as a string date...not very clever

I am working on a fix at the moment and I am sure it will work..basically I am going to add the date correctly as ticks..

Thank you so much for your assistance... it is much appreciated

Regards

 

[Chris2]

You can use a Text data type to store datetimes in SQLite if you prefer, but you need to use an ISO8601 format - e.g. "YYYY-MM-DD HH:MM:SS.SSS".
[section 2.2 - https://sqlite.org/datatype3.html]

 

[Pelky]

Thank you Chris. I realise where I have gone wrong so beside using a large booted foot to kick myself I have had to create a work-around. Not the best way but possibly the simplest. Just created a simple memory file and used that.

Thank you so much for your response...