How to delete Records on mySQL of an entire month-year

Databases
1

Hi team!

I’m trying to delete all the records of one month-year. Example: January 2015.

I’m choosing this value from a Listbox, that have this form: 2015/01

And Im using this syntax, but nothing happens:
Dim sql As String = “DELETE FROM facturas_” + Selector(1) + " WHERE Comprobante_Fecha BETWEEN DATE(" + FECHA + “) AND DATE(”+ FECHA + “)”

Where Fecha = 2015/01.

What Am I doing wrong?

2

Also I tried with this and nothing:
Dim sql As String = “DELETE FROM facturas_” + Selector(1) + " WHERE Comprobante_Fecha BETWEEN date_format(" + FECHA + “, ‘%Y/%m’) AND date_format(”+ FECHA + “, ‘%Y/%m’)”

3

What database are you using? Also, if you view the raw date data in the sql table, what does it look like (pls post a sample)?

4

MySQL Server

5

For example, I have many records, 10 records on march/2015, 59 on april/2015, 345 on January/2015.

And a Combobox Displays the Year and Month of the loaded table. in this format: 2015/01 for January 2015.
So, If I choose 2015/01. The other listbox will display all the records inherent of this Year/Month.

But now I’m trying to programming If the “2015/01” is chosen then Delete all the records about this year/month.

The dates on column “Comprobante_fecha” has this format: 2015-09-30 10:05:58

6

You could use

WHERE Comprobante_Fecha LIKE ‘2015-01%’

to get the entire month. It’s much easier if you use the sql standard format for dates, as it’s designed for this sort of thing.

7

I use .sqldate rather than trying to force the format in the query. Then you can just query as Tim showed.

Also, the time portion of a MySQL DATE field is significant to 3 decimals, so if you’re running a BETWEEN query you might want to take that into account i.e.:

"... BETWEEN '" + srchStartDate.SQLDate + " 00:00:00.000' AND '" + srchEndDate.SQLDate + " 23:59.59.997'"
8

Thanks all, It works. The Syntax is with “-” not with “/” as I was doing it

9

[quote=225376:@Tim Hare]You could use

WHERE Comprobante_Fecha LIKE ‘2015-01%’

to get the entire month. It’s much easier if you use the sql standard format for dates, as it’s designed for this sort of thing.[/quote]
I probed your Solution and It works on several Machines PCs and Macs.

But doesn’t work on some computers and gives me an Syntax Error.

So I probed the suggested Tanner Lee using sqldate to pass it to the query instead of formatting the text on query