Select between two dates in European Format

Question

The following query doesn't return any results when I try to select records between February and March.

Here is the query that I'm using :

SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM data WHERE  CONVERT(VARCHAR(50),data,105) BETWEEN  CONVERT(VARCHAR(50),'" + dateTimePicker1.Text + "',105) AND CONVERT(VARCHAR(50),'"+dateTimePicker3.Text+"',105)", conn);

data is type VARCHAR(50) and i want to convert it to dd-MM-yyyy format

Do not compare dates converted into `VARCHAR`, convert both dates to `DATETIME` before comparing. — user2989408, Feb 20 '14 at 21:09
@user2989408 i cant unrestand u , can u type the querry , what do you mean don convert both dates ? — user3334406, Feb 20 '14 at 21:11
@user2989408 i already did , if u can see in the querry CONVERT(VARCHAR(50),data,105) PLS help someone i need the whole querry — user3334406, Feb 20 '14 at 21:14
Show us some sample data and also you are converting the dates into `VARCHAR`, to compare you need to convert them into dates. — user2989408, Feb 20 '14 at 21:15
Read @Aaron-Bertrand [blog about not using between](http://sqlblog.com/blogs/aaron_bertrand/archive/2011/10/19/what-do-between-and-the-devil-have-in-common.aspx) — , Feb 20 '14 at 21:23
[Bad habits to kick : mis-handling date / range queries](https://sqlblog.org/2009/10/16/bad-habits-to-kick-mis-handling-date-range-queries) - you should **not** store dates as strings - you're just asking for trouble that way. Use the `DATE` or `DATETIME` datatypes! — marc_s, Feb 20 '14 at 21:51

user2989408 · Accepted Answer · 2014-02-20T21:17:56.213

1

Your SQL should be something like

 SELECT * FROM data 
 WHERE  CONVERT(datetime, data, 105)  >= CONVERT(datetime, '01-01-2014', 105) 
     AND  CONVERT(datetime, data, 105) <= CONVERT(datetime, '01-31-2014', 105)

edited Feb 20 '14 at 21:17

answered Feb 20 '14 at 21:14

user2989408

3,127
1
17
15

Still doenst works, when i want to select data from 01-02-2014 to 01-03-2013 it shows nothing :( – user3334406 Feb 20 '14 at 21:17
Brother, thank u very much it works now i think i will test it more, u just forgot to change (VARCHAR(50),'"+datetimepicker3.text+"',105) to (datetime, '" + dateTimePicker3.Text + "', 105) – user3334406 Feb 20 '14 at 21:21
Glad it worked. I was just showing the SQL you can convert it into C# accordingly. – user2989408 Feb 20 '14 at 21:22
@user3334406 I highly recommend you use a parametarized query also. – Evan L Feb 20 '14 at 21:24

score 0 · Answer 2 · answered Feb 20 '14 at 21:20

I would use date value from DatePicker Once you convert everything to Date SQL will not have problem of comparing mm-dd-yyyy to dd-mm-yyyy

SELECT * 
FROM MyTable
WHERE CONVERT(DATE,MyStringDate,105) >= dateTimePicker1.Value.Date
AND CONVERT(DATE,MyStringDate,105) <= dateTimePicker.Value.Date

Evan L · Answer 3 · 2014-02-20T21:41:10.147

You are doing too many conversions. SQL will have a hard time determining BETWEEN on VARCHAR types.

Instead convert things to DATE or DATETIME (depending on your SQL Server Version and type)

Also you should use parameterized queries to avoid SQL Injection.

Something like this would work well:

using (var da = new SqlDataAdapter())
{
    da.SelectCommand.CommandText = 
         "SELECT * FROM data WHERE CONVERT(datetime, data, 105) >= @Date1 AND CONVERT(datetime, data, 105) <= @Date2";
    //Use the DateTimePicker's Value property here, not the Text Property
    da.SelectCommand.Parameters.AddWithValue("@Date1", dateTimePicker1.Value);
    da.SelectCommand.Parameters.AddWithValue("@Date2", dateTimePicker3.Value);
    da.Fill(yourDataTable); //Or whatever you're doing
}

Select between two dates in European Format

3 Answers3