CODEWITHSUNDEEP
sqlsql-servervb.net
James
James

Reputation: 567

SQL SELECT Between 2 Dates

I am having a real issue with trying to query my database by selecting the data between 2 dates.

Below is the query I am using within my VB .NET project.

WHERE [BuildID] > 0 AND [EndDate] BETWEEN '01/07/2014' and '31/07/2014'

The date format is set to dd/mm/yyyy and the column type is VARCHAR(10)

When I run this query, it brings back all the data in my database, basically it selects all.

I have tried several methods such as [EndDate] >= '01/07/2014' and [EndDate] <= '31/07/2014' and it brings back the same result, it disregards the dates and brings back the entire database.

I have a feeling that it is because the column is set to VARCHAR but I am not sure.

If someone could point me in the right direction that would be most helpful.

Thanks for taking the time to read this.

Upvotes: 1

Views: 5753

Answers (5)

Bj&#248;rn-Roger Kringsj&#229;
Bj&#248;rn-Roger Kringsj&#229;

Reputation: 9981

Always always use parameterized SQL queries.

Dim date1 As Date = Date.Parse("2014-07-01")
Dim date2 As Date = Date.Parse("2014-07-31")

mycmd.CommandText = ".... WHERE [BuildID] > @bid AND [EndDate] BETWEEN @firstDate AND @lastDate"
mycmd.Parameters.AddWithValue("@bid", 0I)
mycmd.Parameters.AddWithValue("@firstDate", date1)
mycmd.Parameters.AddWithValue("@lastDate", date2)

Upvotes: 1

ngrashia
ngrashia

Reputation: 9894

Refer Fiddle for date time formats in SQL Server

Use something like:

convert(varchar,EndDate,111)  BETWEEN '2014/07/01' and '2014/07/31'

Because, varchar does string comparison and will fail in dd/mm/yyyy format. String comparison works best in yyyy/mm/dd (YearMonthDate) format.

Example, as per varchar comparison,

31/01/2014 is greater than 01/02/2014
whereas,
2014/01/31 is lesser than 2014/02/01.

Upvotes: 1

Nik Bo
Nik Bo

Reputation: 1410

At first change the column type to date or if you need the time too, use datetime.

Then you can use the T-SQL Function CONVERT to change your submitted Value to the right Time/Datetime Format.

T-SQL Convert

Upvotes: 1

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

Try yyyyMMdd format which will never fail. You should also try converting your column to DATETIME.

WHERE [BuildID] > 0 AND CONVERT(DATETIME,[EndDate]) BETWEEN '20140701' and '20140731'

or if you have a variable in your VB then :

dateVar.ToString("yyyyMMdd")

Upvotes: 1

JohnLBevan
JohnLBevan

Reputation: 24410

The column type is VARCHAR(10)

To have dates treated as dates, you'd need the column type to be date (or datetime if using older versions of SQL).

If you don't have control over the column type, you'd need to cast to date, but that's going to perform badly with any sizable data set:

WHERE [BuildID] > 0 
AND cast([EndDate] as date) BETWEEN '2014-07-01' and '2014-07-31'

NB: I also changed the date format of the strings above - that's not required, but it's my preference to avoid confusion between US and UK formats (especially useful if your code's maintained by an international team).

Upvotes: 2

Related Questions