Why do you think the query should return only one row (or none) regardless of how many rows may or may not match the filter?
I'm not sure if this statement
CONVERT(VARCHAR(11), LTRIM(RTRIM('01-JUL-2010')), 106)
actually does anything at all. Try this:
SELECT CONVERT(VARCHAR(11), LTRIM(RTRIM('41-JUL-2010')), 106).
The string dates cannot be sensibly compared as they stand. They must be converted to proper dates for SQL Server to be able to compare them properly. Both the columns and the variables. Here's some stuff for you to play with:
create table #trp_table (from_date varchar(15),to_date varchar(15))
insert into #trp_table(from_date,to_date)
SELECT '01-JUL-2010', '22-JUL-2010' UNION ALL
SELECT '15-JUL-2010', '20-JUL-2010' UNION ALL
SELECT '11-FEB-2010', '27-JUL-2010'
DECLARE
@To_Date varchar(15), @from_date varchar(15),
@To_DateDT DATETIME, @from_dateDT DATETIME
SET @from_date = '01-JUN-2010'
SET @To_Date = '30-JUN-2010'
SET @from_dateDT = CONVERT(DATETIME, @From_Date, 106)
SET @To_DateDT = CONVERT(DATETIME, @To_Date, 106)
-- check variables are good:
SELECT FromDate = @from_dateDT, ToDate = @To_DateDT
-- check date ranges:
SELECT From_date, To_date,
Errors = CASE WHEN (From_date >= @from_dateDT AND From_date <= @To_DateDT)
AND (To_date >= @from_dateDT AND To_date <= @To_DateDT) THEN 1 ELSE 0 END
FROM ( -- convert string to datetime in this inner query or derived table
SELECT
From_date = CONVERT(DATETIME, From_Date, 106),
To_date = CONVERT(DATETIME, To_Date, 106)
FROM #trp_table
) d
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden