vb.net with mysql how to pick between to dates of different years?

Question

I have a vb.net program that queries a mysql database. i can search and get records between to dates with this code:

sqlQRY12 = "SELECT * from mating WHERE date BETWEEN '" & export_daily_date_DateTimePicker1.Text & "' AND '" & export_daily_date_DateTimePicker2.Text & "' AND chkbox = '0' ORDER BY lot_id ASC"

my format for my date is:

        export_daily_date_DateTimePicker1.Format = DateTimePickerFormat.Custom
    export_daily_date_DateTimePicker1.CustomFormat = "MM/dd/yy"

    export_daily_date_DateTimePicker2.Format = DateTimePickerFormat.Custom
    export_daily_date_DateTimePicker2.CustomFormat = "MM/dd/yy"

however if i try to search between two years like 12/20/13 - 02/20/14 I return no records when i know they exist? Any help would be great ty

Answer 1

Generally dates expressed as strings in database queries should be in the format "YYYY-MM-DD".

You essentially want your executed query to be this:

SELECT * from mating
WHERE date BETWEEN '2013-12-20' AND '2014-02-20'

So change the format of your dtpickers to be yyyy-mm-dd like this:

export_daily_date_DateTimePicker1.Format = DateTimePickerFormat.Custom
export_daily_date_DateTimePicker1.CustomFormat = "yyyy-MM-dd"

export_daily_date_DateTimePicker2.Format = DateTimePickerFormat.Custom
export_daily_date_DateTimePicker2.CustomFormat = "yyyy-MM-dd"