whats the problem with this query?

Please redit thisquery if there are any problemin that :

SELECT [travel], [s开发者_如何转开发ervice], [travelid], [fro_m], [t_o], [dep], [arr], [booking_closed],
[discount], [faresleeper], [rating], [seats], [s_no], 
[fare] = 
CASE 
  WHEN @date >= Cast(Convert(varchar(20),s1from,101) as datetime
    AND @date >= Cast(Convert(varchar(20),s1to,101) as datetime 
  THEN s1rate 
  ELSE fare 
END 
FROM a1_volvo WHERE  ( fro_m = @fro_m ) AND ( t_o = @t_o ) 

---

SELECT [travel], [service], [travelid], [fro_m], [t_o], [dep], [arr], [booking_closed],
[discount], [faresleeper], [rating], [seats], [s_no], 
[fare] = 
CASE 
  WHEN @date >= Cast(Convert(varchar(20),s1from,101) as datetime)
    AND @date >= Cast(Convert(varchar(20),s1to,101) as datetime )
  THEN s1rate 
  ELSE fare 
END 
FROM a1_volvo WHERE  ( fro_m = @fro_m ) AND ( t_o = @t_o ) 

---

In the WHEN statement, should'nt it be

WHEN @date >= Cast(Convert(varchar(20),s1from,101) as datetime

AND @date <= Cast(Convert(varchar(20),s1to,101) as datetime

with one greater equal and one lesser equal if it is a range check?

---

Check your cast it should be cast(column as datatype) and also consider the greater than equal to ">=" and lesser than equal to "<=" as what il_guru have said. :) Check this out...

SELECT [travel], [service], [travelid], [fro_m], [t_o], [dep], [arr],
  [booking_closed], [discount], [faresleeper], [rating], [seats], [s_no],      
  [fare] = CASE WHEN @date >= Cast(Convert(varchar(20),s1from,101) as datetime)
       AND @date <= Cast(Convert(varchar(20),s1to,101) as datetime)
       THEN s1rate ELSE fare END  
FROM a1_volvo  

-- use this where clause if fro_m and t_o are not datetime
WHERE (fro_m = @fro_m ) AND ( t_o = @t_o )

-- else use this where clause if fro_m and t_o have datetime of datatypes
WHERE ( Cast(Convert(varchar(20),fro_m,101) as datetime) = @fro_m ) 
AND (  Cast(Convert(varchar(20),t_o,101) as datetime) = @t_o )

---

Delete [fare] = and try again.