• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:


Please find the attached code utilized in a WHERE clause for a Gridview.   The Gridview displays;  however,  it is not filtering properly.   The only filter that is working is the "station" the "time" range and "date" ranges are not functioning.
1 Solution
It's not functioning because you are using "&lt;" and "&gt;" in your SQL statements instead of "<" and ">".

Replace "&lt;" with "<" and "&gt;" with ">" in your SQL statement and it should work.

"&lt;" and "&gt;" is commonly used when writing HTML markup, but does not work for SQL queries.
käµfm³d 👽Commented:
"&lt;" and "&gt;" is commonly used when writing HTML markup
Ah, mon ami, but that is exactly what you are seeing:  ASP.NET markup.

I think this is a logic error. You have numerous AND & OR in your query. But you haven't grouped any of them together (via parentheses). This means you need to worry about precedence. If I recall correctly, AND binds tighter than OR, so anywhere that you have "something AND something-else OR another-something-else", you have effectively written:

(something AND something-else) OR another-something-else

Note the additional parens.

You need to employ a strategic placement of parentheses to force the query engine to use the precedence that you intended to occur.


This is (effectively) what the query engine interprets your current query as:

[([Dates] &gt;= @Dates) AND ([Dates] &lt;= @Dates2) AND ([Dep] &gt;= @Dep) AND ([Dep] &lt;= @Dep2) AND ([Station] = @Station) AND (Code = 'MC')]

OR (Code = 'MM')
OR (Code = 'MA')
OR (Code = 'MF')
OR (Code = 'MH')
OR (Code = 'ML')
OR (Code = 'MN')

* Brackets removed from "Code" to satisfy the EE tag parser.
without changing the effect of your current select query, but reformatting it, it could appear like this
SELECT [ID], [Dates], [Ship], [Station], [Otbd], [Dep], [Code], [Time], [Code1], [Time1], [Wake], [Call], [MM], [RB], [Reason], [ATA], [RON], [GT], [Captain]
FROM [Delays]
		([Dates] >= @Dates)
		AND ([Dates] <= @Dates2)
		AND ([Dep] >= @Dep)
		AND ([Dep] <= @Dep2)
		AND ([Station] = @Station)
		AND ([Code] = 'MC')
		OR ([Code] = 'MM')
		OR ([Code] = 'MA')
		OR ([Code] = 'MF')
		OR ([Code] = 'MH')
		OR ([Code] = 'ML')
		OR ([Code] = 'MN')

Open in new window

I think this is an excellent opportunity to use IN () for that list of codes (it will help you avoid some of the precedence issues).

Additionally I would suggest that all the parentheses except those required for the IN () are currently redundant. That is, this would work equally as well without the current parentheses.
FROM [Delays]
WHERE [Dates] >= @Dates
        AND [Dates] <= @Dates2
        AND [Dep] >= @Dep
        AND [Dep] <= @Dep2
        AND [Station] = @Station
        AND [Code] IN ('MC', 'MM', 'MA', 'MF', 'MH', 'ML', 'MN')
        [Dates] DESC

Open in new window

Q1. Now that we can see it a bit more clearly, now we need to ask ourselves, will all the parameters have values all the time?

Q2. Is the field type for [Dates] datetime2, datetime or smalldatetime, and do the records have values such as:
2013-09-07 23:44:55
i.e. do they store time other than 00:00:00

btw: you could just paste your raw code snippet into a comment - look for the word "code" in the toolbar - the script text should fit between the code tags.
NB: Word's curly quotes can nicely mess-up script :)
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

QlemoBatchelor and DeveloperCommented:
  [Dates] >= @Dates  AND [Dates] <= @Dates2
is exactly the same as
   [Dates] between @Dates and @Dates2
which is more clear (same for Dep), so the reworked WHERE would look like this:
WHERE [Dates] between @Dates and @Dates2
  AND [Dep] between @Dep and @Dep2
  AND [Station] = @Station
  AND [Code] IN ('MC', 'MM', 'MA', 'MF', 'MH', 'ML', 'MN')

Open in new window

On an additional note, take care in regard of dates and "between"-like conditions - the boundaries are included, which often is not what you want to have, because in
/* Query 1 */ .... where dt between @date1 and @date2 ...
/* Query 2 */ .... where dt between @date2 and @date3 ...

Open in new window

you would get the same row matching @date2 in both queries.
the reason for my question Q1 and Q2 was to see if there is an inherent risk of the query being wrong :(

yes, you could use between - but I hate it for date ranges - and there is absolutely nothing inherently wrong with using >=  together with <=
although it may well need parentheses depending on answers to Q1 & Q2 IMHO

Beware of Between it's not always a good friend.
QlemoBatchelor and DeveloperCommented:
But there is no function difference using >= AND <= or BETWEEN - en contraire, the former obfuscates the intention. between makes it more clear what you think you ask for (of course that might not be what you get).
we therefore disagree on the clarity
I believe >= with <=
is actually much more definitive (and clear)

there is a great deal of misunderstanding about the word between in the context of SQL simply because in common language it can have 3 basic meanings:

to fit between (> with <)
to fly between (>= with <=)
group of z between x and y (|>= with <| > with <=|)

clearly you know the SQL between is >= with <= and hence accommodate it: I'm not suggesting otherwise.
QlemoBatchelor and DeveloperCommented:
That pretty much covers everything ;-)
BOEING39Author Commented:
I appreciate everyone's input on this.  Paul thanks for the edification and finite detail in your response.   I learned something and it is now functioning as needed.
Really pleased it helped. Cheers Paul

ps, see: "Beware of Between"
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now