SQL Date Search

Hello Group. I would like to compare the database dates, with a constructed date, but it doesn’t work.

// Create my date 
Dim DataEsaminata as new date
DataEsaminata.month =05
'messagebox("Confronto la data di calendario =" + dataesaminata.shortDate)

Var rows As RowSet
dim DataFormattata as new date

  rows = db.SelectSQL("SELECT * FROM Promemoria where Data="+dataesaminata.shortDate)
  If rows <> Nil Then
  For Each row As DatabaseRow In rows
         messagebox "Trovata data=" + dataesaminata.shortDate

Where am I wrong?

Use .SQLDate instead of .ShortDate.

And that SQL would generate an error anyway because you aren’t quoting it. Try it like this instead (assuming SQLite):

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

rows = db.SelectSQL(“SELECT * FROM Promemoria where Data=”+dataesaminata.SQLDate)


rows = db.SelectSQL(“SELECT * FROM Promemoria where ID=16”)

Program RUN.

SURE, the problem is comparsion between dates.

You need to change that line of code.

Guys sorry, I don’t understand. How should I quoting SQL?

Can you change this:

to this:

Maybe you have an error in another place too. If you post a sample project maybe someone can take a look.

Did you try the way I posted it?

rows = db.SelectSQL(“SELECT * FROM Promemoria WHERE Data=’”+dataesaminata.SQLDate + “’”)

Ok i change the code in:

rows = db.SelectSQL(“SELECT * FROM Promemoria WHERE Data=’”+dataesaminata.SQLDate + “’”)

But return an error . I USE SHORT DATE.

Your image doesn’t include information about what the error is, so is hard to know. Maybe you are not connected at this point to the db?

1 Like

It’s a problem with the date because if I replace with … where ID = 16 it works.

If you can share a sample project, I can take a look and let you know what needs to be changed.

CERTAINLY, only that I have the project on my pc in the office, tomorrow I’ll copy it and update the link. Thanks.

Il valore della data da confrontare (SQLDate) va racchiuso tra apici.

Se stai eseguendo la query su Access bisogna utilizzare DATEVALUE (sto andando a ricordi)
“SELECT * FROM Promemoria where Data=DATEVALUE(’”+dataesaminata.SQLDate + “’)”
Prova così…

nothing, it gives me an error even with the string:

rows = db.SelectSQL(“SELECT * FROM Promemoria where Data=DATEVALUE(”+dataesaminata.SQLDate + “)”)

I am attaching the project, so you can see where the error is. TEST.rar | Files.fm.

What is the contents of error.Message ?

WARNING to helpers:

The project uses “Microsoft Access”…

Guys, maybe I understand the problem. If I read the date from the database without formatting it, it comes out as LongDate. So I should be able to format it in the query so that it reads as ShortDate and be able to compare it with the search query. It can be done ?

So, I think this is the problem. I display the short date, but in the DB it is recorded as long / complete … when I enter the comparison date which is short, it does not find it.