create table trp_table (from_date varchar(15),to_date varchar(15))
insert into trp_table(from_date,to_date)
select CONVERT(VARCHAR(11), LTRIM(RTRIM('01-JUL-2010')), 106), CONVERT(VARCHAR(11), LTRIM(RTRIM('22-JUL-2010')))
UNION ALL
SELECT CONVERT(VARCHAR(11), LTRIM(RTRIM('15-JUL-2010')), 106), CONVERT(VARCHAR(11), LTRIM(RTRIM('20-JUL-2010')))
UNION ALL
SELECT CONVERT(VARCHAR(11), LTRIM(RTRIM('11-FEB-2010')), 106), CONVERT(VARCHAR(11), LTRIM(RTRIM('27-JUL-2010')))
DECLARE @lb_error_flag BIT
SELECT @lb_error_flag = CASE WHEN (from_date >= CONVERT(VARCHAR(11), LTRIM(RTRIM( '01-JUN-2010')), 106) and from_date <= CONVERT(VARCHAR(11), LTRIM(RTRIM( '30-JUl-2010')), 106) )
AND (to_date >= CONVERT(VARCHAR(11), LTRIM(RTRIM( '01-JUN-2010')), 106) and to_date <= CONVERT(VARCHAR(11), LTRIM(RTRIM( '30-JUN-2010')), 106)) THEN 1 ELSE 0 END
FROM trp_table
print @lb_error_flag
Still it returns 1... where it should be 0.. 3rd row differ from the from_date and to_date.....
and select select CONVERT(VARCHAR(11), LTRIM(RTRIM('01-JUL-2010')), 106) works in my environment....
select isdate(CONVERT(VARCHAR(11), LTRIM(RTRIM('01-JUL-2010')), 106)) returns 1