How to Get data between 2 dates in Sql

Question

Hi All,
I have 1 table with foll data:

LineNo  Name        Month_Year
1           TOM          Apr-2011
2           John          Mar-2012
3            Vijay          May -2011
4           Sakshi        June -2011

Now, i want data between date range  '01-Apr-2011'  and  '30-Mar-2012'
My Query:

Declare @FromDate as Datetime
Declare @ToDate as Datetime

Set @FromDate = '01-Apr-2011'
Set @ToDate =  '30-Mar-2012'

SELECT * FROM TABLE1
WHERE 
CHARINDEX(left(MONTHYEAR,3),'XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC')/3 
between month(@FromDate) and month(@ToDate) 
and
right(MONTHYEAR,4) between year(@FromDate) and year(@ToDate)

But using this query i does not get 3 line. I know that there is proble in month range.

Anybody have bettter idea and solution. Kindly help me.