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.

Santhosh Kumar Jayaraman · Answer

try this..My column name is month_year and tablename is test Declare @FromDate as Datetime Declare @ToDate as Datetime Set @FromDate = '01-Apr-2011' Set @ToDate = '30-Mar-2012' SELECT * FROM test WHERE (CHARINDEX(left(MONTH_YEAR,3),'XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC')>=CHARINDEX(left(datename(month,@FromDate),3),'XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC') and YEAR(@FromDate)=substring(Month_year,CHARINDEX('-',month_year,0)+1,len(month_year)) or CHARINDEX(left(MONTH_YEAR,3),'XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC') and YEAR(@ToDate)=substring(Month_year,CHARINDEX('-',month_year,0)+1,len(month_year)))

Pravinkumar Birajdar · Answer

select * from Table1 where Month_Year>='04/01/2011' and Month_Year<='03/30/2012'