BETWEEN clause in SQL

Question

I have a SQL statement to display data between two dates. I almost got it but there's a problem.

If I input March 1,2012 to March 7, 2012.. it should show data with dates between the two.. but it also show all of the dates under March 2012.. but whenever I input March 10, 2012 to March 30, 2012 the SQL works perfectly.. any help will be appreciated. thanks

SELECT 
   agentname, noofcalls, qualified, booking, resched, 
   actualbooking, sales, remarks, 
   concat(month,' ',day,',',year) as 'date' 
FROM 
   tblagents
WHERE 
   (month between '" & cbosmonth.Text & "' AND '" & cboemonth.Text & "')
   AND (day between '" & cbosday.Text & "' AND '" & cboeday.Text & "')
   AND (year between '" & cbosyear.Text & "' AND '" & cboeyear.Text & "')"

Why don't you store a date in a single field (with appropriate for date arythmetics type)? — zerkms, Dec 14 '12 at 09:32
This looks like an [SQL Injection](http://en.wikipedia.org/wiki/SQL_injection) attack waiting to happen. — RobEarl, Dec 14 '12 at 09:33
@user1841167: http://msdn.microsoft.com/en-us/library/ms186724.aspx — zerkms, Dec 14 '12 at 09:34
It would be better to store to a date to the database instead of 3 separate integers. You could then converter you comboboxes' values into a Date object and compare this. — Ash Burlaczenko, Dec 14 '12 at 09:35
@zerkms does this work even though my dates are concatenated? — user1841167, Dec 14 '12 at 09:38
@AshBurlaczenko but the thing is my date are concatenated... i've done it on datetimepicker but nothing happend — user1841167, Dec 14 '12 at 09:39
Can you edit the question with the database version, and the application language used? — SWeko, Dec 14 '12 at 09:46

palako · Answer 1 · 2012-12-14T10:09:42.063

3

you are doing string comparisons in each of your 'between'. Every number starting with a 1, a 2 or a 3, regardless of what follows it, i.e. 21, or 26, or 31, they are all lower than 7 if you look at them as strings. 1 to 30 works because you're only leaving 31 behind, and 30 < 31 as a String as well.

Do the concatenation first and then the between:

WHERE concat(month,' ',day,',',year) 
      BETWEEN concat(cbosmonth.Text,' ', cbosday.Text,' ',cbosyear.Text)
      AND concat(cboemonth.Text,' ', cboeday.Text,' ',cboeyear.Text)

(check out for correct syntax, I'm just copy pasting from your question, not tried it)

BTW, unless you have a reason to, you probably should be storing the entire date in a single column with the right data time (datetime, timestamp, ...) and not three separated columns.

edited Dec 14 '12 at 10:09

answered Dec 14 '12 at 09:35

palako

3,342
2
23
33

I've tried the codes but I got the 'Operand should contain one column(s)' error... i have no date column in my database.. the date only comes from the concatenation of the three columns... Thanks – user1841167 Dec 14 '12 at 09:49
you have a date alias in your SELECT. – palako Dec 14 '12 at 09:56
ah, my bad, you can't refer to the alias in the WHERE clause because it's executed before the column value is determined. I'll edit my answer. – palako Dec 14 '12 at 10:07

score 0 · Answer 2 · answered Dec 14 '12 at 09:44

0

That is the incorrect way to store a date in database. Change the date column to datatype date.

You could then do:

SELECT *
FROM table
WHERE bookingDate between to_date ('2012/03/01', 'yyyy/mm/dd')
AND to_date ('2012/03/07', 'yyyy/mm/dd');

answered Dec 14 '12 at 09:44

atripathi

898
3
11
17

score 0 · Answer 3 · edited May 23 '17 at 11:48

This approach is wrong.

In order for a date to be between two interval dates it does not have to have a day number between the two dates, e.g. (pseudocode)

date = May-25-2012; startDate = March-15-2012, endDate = June-01-2012
date is clearly between startDate and endDate, yet
day(date) is 25, which is not between day(startDate) = 15 and day(endDate) = 1
more, as 15 is larger than 1, there are no numbers between them, so that condition will always be false

Similar example can be made for the month part of the date (e.g. date = May-25-2012; startDate = September-15-2010, endDate = Match-01-2015)

You need to take the values for day, month, year, and construct a Date either in the application or on the server and use that to compare the values against.

To make a date from text fields in VB

Dim startDate = new DateTime( 
      Convert.ToInt32(cbosyear.Text), 
      Convert.ToInt32(cbosmonth.Text), 
      Convert.ToInt32(cbosday.Text))

Note that this will fail if the user enters, e.g. "some text" for the year value. You'll need to add some data validations to achieve that.

To make a datetime from parts in SQL Server, take a look here, there are quite a few techniques explained.

Also, you should always avoid just pasting values into the sql string, that's asking for sql injection problems. You should do something like this:

Dim command = new SqlCommand()
command.CommandText = "SELECT .... FROM tblagents where DATEFROMPARTS(year, month, day) between @startDate AND @endDate"
command.Parameters.AddWithValue("@startDate", startDate)
command.Parameters.AddWithValue("@endDate", endDate)

Oh I just realized what you said.. I'll try to construct a Date.. BTW, app language is vb.net and sql server as my database — user1841167, Dec 14 '12 at 09:55

BETWEEN clause in SQL

3 Answers3

Linked

Related