Retrieving dates from SQLIte

Post Reply
Bushbug
Posts: 6
Joined: Thursday 08th November 2018 7:01pm

Retrieving dates from SQLIte

Post by Bushbug » Friday 21st August 2020 6:27pm

I have a program that requires retrieving a date from SQLite3 and placing the value into a datebox. When I run the Select statement "SELECT PDate FROM TableName WHERE Registration = "PJA NA" in SQLite itself (Execute SQL), it returns the correct date as shown in the specific field. However, If I put the same SQL Statement in my Gambas3 program to retrieve the PDate from the Table it returns a completely incorrect date including a time.
Here is the actual statement: "Select PDate, VMake, VMiles from Vehicles Where Vreg = " & "'" & sVeh & "'"
The date returned should be 12/04/2019 but what is actually returned is 20/04/1912 00:00:00
Can anyone shed some light on the problem please?

User avatar
Quincunxian
Posts: 95
Joined: Sunday 25th June 2017 12:14am
Location: Western Australia

Re: Retrieving dates from SQLIte

Post by Quincunxian » Saturday 22nd August 2020 12:43am

Hi BushBug,
I'll make the assumption that you are running the latest Gambas 3 version.

What date 'Mode' do you have set for the control ?

Can you show the code where you are applying the returned value to the control ?
MyDataBox.Value = ?
If you are using a variable for this, how is it declared ?
ie: passing the value back from a subroutine or function.
or
Are you doing this directly after the query ?
MyDateBox.Value = MyResult!PDATE


Minor thing and nothing to do with the issue you are having.
Always put your WHERE clauses in brackets : Where(Vreg = " & "'" & sVeh & "'")
where you have more than one comparison - get into the habit now as with more
complex queries it can lead to some perplexing errors especially when you are doing multiple joins.
Cheers - Quin.
I code therefore I am

Bushbug
Posts: 6
Joined: Thursday 08th November 2018 7:01pm

Re: Retrieving dates from SQLIte

Post by Bushbug » Saturday 22nd August 2020 8:09am

Thank you very much for your response Quincunxian. In reply to your questions:
1. The mode of the Datebox is set to Dateonly.
2. I am using a variable "dPurchase" which is declared as a Date. (Dim dPurchase As Date)
3. I am applying the returned value directly from the query. (dPurchase = $rData1!PDate)
4. This is the code for applying the value to the control.

sSQL3 = "Select PDate, VMake, VMiles from Vehicles Where Vreg = " & "'" & sVeh & "'"
dtbPurDt.ReadOnly = False
dtbPurDt.Value = dPurchase

It puts a value of 20/04/1912 in the control instead of 12/04/2019.

_______________________________________________________________________________________________________

Interestingly, in another part of the program (different form) I have a similar query and it works just fine.

sSQL = "Select TransDate, Odometer from VehicleData Where VehType =" & "'" & sReg & "'" & "Order by Odometer Desc"

$sRes = MODMain.$Con.Exec(sSQL)

dDate = $sRes!TransDate

dtbTransDate.ReadOnly = False

lblTransDate.text = "Last transaction date"

dtbTransDate.Value = dDate

User avatar
Quincunxian
Posts: 95
Joined: Sunday 25th June 2017 12:14am
Location: Western Australia

Re: Retrieving dates from SQLIte

Post by Quincunxian » Sunday 23rd August 2020 12:52am

Can you copy the second query where it works, to populate the datebox that has the error ?
My thinking is around trying to determine if the error is from the underlying data in the SQL database.
Everything else, from what I can see is ok.
Cheers - Quin.
I code therefore I am

Bushbug
Posts: 6
Joined: Thursday 08th November 2018 7:01pm

Re: Retrieving dates from SQLIte

Post by Bushbug » Sunday 23rd August 2020 9:23am

Good morning Quincunxian. Thank you for pointing me in the right direction. I deleted some records in the database table and re-saved them. Then the program worked perfectly. Obviously some date data in the specific table had somehow become corrupted.
Your assistance is greatly appreciated. Now I just want to try and "publish" the program so that I can send it to a friend to use. Let's see if I can do it successfully.
Best regards.

Post Reply