I need help for Year(date)

[Federico_Giannetti]

Good evening everyone, I’m starting to use Xojo again after many months of downtime. I have a problem with the syntax… I pass two string values: DataDaEliminareDallatabella which tells me the year of the records that I should delete from the table, and TavoloDaCuiEliminareRecords which tells me the name of the table, but something is wrong.
The table field Data_Spedizione is of the type dd/mm/YYYY

Var deleteSQL As String
deleteSQL = "DELETE FROM " + TabellaDaCuiEliminareRecords + " WHERE Year(Data_Spedizione)=#"+ DataDaEliminareDallatabella +"#"
Try
  db.ExecuteSQL(deleteSQL)
Catch error As DatabaseException
  MessageBox(db.ErrorMessage)
End Try

[AlbertoD]

@Federico_Giannetti

but something is wrong

Do you get an error?
What SQL database are you using?
I don’t know what is the use of # before/after DataDaEliminareDallatabella

[Michael_Eckert]

Should it be ’ instead of #?

[Marius_Dieter_Noetzel]

You should do:

db.ExecuteSQL("DELETE FROM `" + TabellaDaCuiEliminareRecords + "` WHERE YEAR(`Data_Spedizione`) = ?", DataDaEliminareDallatabella)

Think about escaping the tablename. I would never ever put variable content into an SQL-String without escaping it. NEVER!

[Marius_Dieter_Noetzel]

After I was asked about this, a short addition.
I would not use this syntax in this way. A database will perform better with a query that uses a comparison than extracting the year for each entry.
So:

SELECT * FROM `table` WHERE `datefield` BETWEEN '2023-01-01' AND '2023-12-31';

[Jeff_Tullin]

@AlbertoD

I don’t know what is the use of #

A giveaway tell of code that started life in Microsoft Access.

[MarkusR]

something like this?

@Federico_Giannetti

Try
  db.ExecuteSQL( "DELETE FROM " + TabellaDaCuiEliminareRecords + " WHERE Year(Data_Spedizione)=?",val(DataDaEliminareDallatabella))
Catch error As DatabaseException
  MessageBox(db.ErrorMessage)
End Try

The table field Data_Spedizione is of the type dd/mm/YYYY

if it is not a date or timestamp field you need to cast a string to a date before using the year.

[Federico_Giannetti]

NOT WORK, error is: [Microsoft][Driver Manager ODBC] Lunghezza della stringa o del buffer non valida.

[Federico_Giannetti]

@MarkusR

if it is not a date or timestamp field you need to cast a string to a date before using the year.

92 / 5.000

I tried your code, it doesn’t give me any errors, but it doesn’t delete anything, what could be the cause?

[Federico_Giannetti]

Good morning group, and first of all thank you all for the support, as always encouraging for me. I have been using a Microsoft Access database for many years now and it is full of data, so I am afraid to change it. I must say however that Xojo supports it well so far, and therefore I am able to use it well. The field is of type DATE in my table, I have to delete records that have a certain year in common based on the year that is indicated to me. So I have to delete the year 2002 from the database if I am asked for that year, so for example the date 02/01/2002, the date 05/07/2002 but not the date 04/05/2003 Sorry, I am not a professional programmer, so for me certain things are not obvious. Up to now I have solved all the problems with the dates … but now I can’t understand how to tell the program to delete all the data that have the year of the Data_Spedizione field equal to the one I need.

[Jeff_Tullin]

A lot depends upon how the Access DateTime fieds are represented in the ODBC driver you use.
They are accessed using syntax like #yyyy-MM-dd# in Access itself.
They may be exposed as strings in the driver

So you may have some luck trying:

dim txtStart as string =  "#" + format(DataDaEliminareDallatabella, "0") + "-01-01#
dim txtEnd as string =  "#" + format(DataDaEliminareDallatabella+1, "0") + "-01-01#

db.ExecuteSQL( "DELETE FROM " + TabellaDaCuiEliminareRecords + " WHERE Data_Spedizione >= " + txtStart + " and Data_Spedizione < " + txtEnd

or by generating these start/end dates as Xojo dates, and then using them in a parameterised query (always best…) in the expectation that the parameter will do whatever conversion is required

[Federico_Giannetti]

Hi Jeff, i test your code and return this errors

[Marius_Dieter_Noetzel]

try:

@Jeff_Tullin

dim txtStart as string =  "#" + DataDaEliminareDallatabella + "-01-01#"
dim txtEnd as string =  "#" + format(val(DataDaEliminareDallatabella)+1, "0") + "-01-01#"

db.ExecuteSQL( "DELETE FROM " + TabellaDaCuiEliminareRecords + " WHERE Data_Spedizione >= " + txtStart + " and Data_Spedizione < " + txtEnd)

[Federico_Giannetti]

@Jeff_Tullin

dim txtStart as string =  "#" + DataDaEliminareDallatabella + "-01-01#"
dim txtEnd as string =  "#" + format(val(DataDaEliminareDallatabella)+1, "0") + "-01-01#"

db.ExecuteSQL( "DELETE FROM " + TabellaDaCuiEliminareRecords + " WHERE Data_Spedizione >= " + txtStart + " and Data_Spedizione < " + txtEnd)

Thanks Jeff, thanks Marius … the code work fine.
I’d like to understand why Jeff’s code didn’t work.

[Marius_Dieter_Noetzel]

@Federico_Giannetti

I’d like to understand why Jeff’s code didn’t work.

1. Your Variable “DataDaEliminareDallatabella” is string, and not integer.
2. there were missing " at the end of the code
3. there was a missing ) at the end of the code

[Federico_Giannetti]

@Marius_Dieter_Noetzel

• Your Variable “DataDaEliminareDallatabella” is string, and not integer.
• there were missing " at the end of the code
• there was a missing ) at the end of the code

Thanks.

[Jeff_Tullin]

… the pitfalls of typing code in from a phone,from memory, while away from the office.

[Federico_Giannetti]

No problem Your code works fine now
thanks for your time. And thanks to everyone for the support.

[MarkusR]

Val only converts number strings as example “2002”, I guess the input was a date string with day month year, val would return 0