How to Get data between 2 dates in Sql

Pravin Ghadge

14y2.9kSQL

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.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Santhosh Kumar Jayaraman14y ago
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')<=CHARINDEX(left(datename(month,@ToDate),3),'XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC')
and YEAR(@ToDate)=substring(Month_year,CHARINDEX('-',month_year,0)+1,len(month_year)))
Pravinkumar Birajdar14y ago
select * from Table1 where Month_Year>='04/01/2011' and Month_Year<='03/30/2012'