SQL SELECT Between 2 Dates

Question

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.

Have you tried specifying the date in yyyyMMdd format? You will never get it wrong that way. — Giannis Paraskevopoulos, Jul 31 '14 at 08:52
for date comparison using strings, always use YYYY/MM/dd format - year first followed by month followed by date. — ngrashia, Jul 31 '14 at 08:52
Why have a datetime value stored in a `VARCHAR(10)` field? If you only want the date portion, either use `DATE` data type available in SQL 2008 and up, or use a `DATETIME` stripping the time portion. — Giannis Paraskevopoulos, Jul 31 '14 at 09:07

score 2 · Accepted Answer · answered Jul 31 '14 at 08:52

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).

score 1 · Answer 2 · answered Jul 31 '14 at 08:54

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")

score 1 · Answer 3 · answered Jul 31 '14 at 08:55

1

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

answered Jul 31 '14 at 08:55

Nik Bo

1,410
2
17
29

score 1 · Answer 4 · answered Jul 31 '14 at 08:57

1

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.

answered Jul 31 '14 at 08:57

ngrashia

9,869
5
43
58

@jyparask: I dont have experience with SQL Server, may be you can try, convert `string(dd/mm/yyyy)->date->string(yyyy/mm/dd)` format. In oracle it would be like, `TO_CHAR ( TO_DATE (MY_VALUE, 'DD/MM/YYYY') , 'YYYY/MM/DD' );` Change syntax as per SQL Server norms – ngrashia Jul 31 '14 at 09:03
In order for this to work you should do it like : `convert(varchar,convert(datetime,103),111)`. This way you convert the expected string to datetime and then convert it buck to string. But this is too much, isn't it? – Giannis Paraskevopoulos Jul 31 '14 at 09:06
@jyparask: I dont think there is a way out. Either you have to maintain in DB as Date format or in DB as yyyy/mm/dd format. The workaround which I know is this. – ngrashia Jul 31 '14 at 09:07

score 1 · Answer 5 · edited May 23 '17 at 12:29

1

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)

edited May 23 '17 at 12:29

Community

1
1

answered Jul 31 '14 at 09:07

Bjørn-Roger Kringsjå

9,849
6
36
64

SQL SELECT Between 2 Dates

5 Answers5