HEL using date comparision in sql

1

Hi group, I have a big problem.
I have an access database with short dates. I perform a comparison in the SQL … where Data = DataFormattata.short and it doesn’t go … if instead in the query I delete the where and execute an If Data = DataFormattata.short … it works.

Var RecordFinder As RowSet
dim DataFormattata as new date

RecordFinder = db.SelectSQL("SELECT * FROM Promemoria where Data="+ dataesaminata.shortdate)


Dim i as integer=0
While Not RecordFinder.AfterLastRow
  DataFormattata=RecordFinder.Column("Data").DateValue
  'if  dataesaminata.shortDate=DataFormattata.ShortDate then
  messagebox "Trovata corrispondenza fra le date " + DataFormattata.ShortDate
  i=i+1
  'end if
  RecordFinder.MoveToNextRow
Wend
messagebox " Ci sono " + i.ToString + " records che soddisfano la ricerca"

RecordFinder.Close
2

As mentioned in the Xojo forum

  1. Use Prepared Statements (your code doesn’t use quotes)
  2. make sure the format of your query matches the format of the data in the table

netiher of which you seem to be doing,

3

On your db Data is 2022-05-18 00:00:00
Your shortdate is 18/5/22

4

what version of Xojo are you using for starters ?

5

this resolves to

RecordFinder = db.SelectSQL("SELECT * FROM Promemoria where Data=18/5/22")

which is syntacilly incorrect

at best you need

RecordFinder = db.SelectSQL("SELECT * FROM Promemoria where Data='"+ dataesaminata.sqldate+"'")

which becomes

RecordFinder = db.SelectSQL("SELECT * FROM Promemoria where Data='2022-05-18'")

NOTE the addition of single quotes…

a prepared statement would still be better… its a good habit to get into

6

Ok Mr, if I knew how to do it I would have done it. I can’t understand the problem. I’m sorry.

7

Mr DaveS, i write your code but return an error the same:

eRRORE2
eRRORE2865×729 23.9 KB

8

Ok and how do I solve?

9

Xojo 1.1

10

You need to review Microsoft Access database specifications to see how you can enter the correct value.
Maybe this will help:

https://support.microsoft.com/en-us/office/dateserial-function-a0128476-83a0-407c-831a-93f2b046f503?ui=en-us&rs=en-us&ad=us

Because it is a Microsoft Access specific format, I can’t help you.

11

There is no Xojo 1.1 - maybe 2022 release 1.1 ?

That said the message in the image in side the red square tells you what’s wrong
The DATA column in the database is a DATE
The value your trying to use for the query is a STRING
That wont work

So we need to make sure they match

try

RecordFinder = db.SelectSQL("SELECT * FROM Promemoria where Data=DATEVALUE('"+ dataesaminata.shortdate + "')" )

the SQL function DATEVALUE takes a string and turns it into a DATE data type

12

I don’t have time to test this against an ODBC database, but I wonder if the built-in “prepared statement” functionality of SelectSQL will do the transformation automatically? So like this:

RecordFinder = db.SelectSQL("SELECT * FROM Promemoria where Data=?", dataesaminata)
13

here is no Xojo 1.1 - maybe 2022 release 1.1?

Sorry, I have Xojo 2022 release 1.1.

After hitting my head on it all day, maybe I settled with

RecordFinder = db.SelectSQL (“SELECT * FROM Reminder where Data = #” + ExaminedDate.SQLDate + “#”)

I continue tomorrow. now unfortunately I have to leave.