CODEWITHSUNDEEP
sql-serverdatabase
Manisha
Manisha

Reputation: 29

SQL: how to retrieve data in month and year range

I have query in which I want the result in month and year range like data should be in between January/2010 and March/2012..

Can anybody help me.. I have done like...:

(DATENAME(MM, c.startdate) >= 'January' and DATEPART(YEAR, c.startdate) >= 2010) and
(DATENAME(MM, c.startdate) <= 'January' and DATEPART(YEAR, c.startdate) <= 2012)

bue still not getting porper result

Thnaks in advance

Upvotes: 3

Views: 3652

Answers (3)

Brad
Brad

Reputation: 645

Select
    ...
Where Dateadd(dd, datediff(dd, 0, c.startdate),0) between '2012-01-01' and '2012-04-01'

EDIT: I'm now assuming that your month and date are the input that you are using.

Declare @month1 varchar(20), @year1 int
  ,@month2 varchar(20), @year2 int
  ,@realmonth1 int, @realmonth2 int
  ,@first int, @input1 datetime, @input2 datetime
Set @month1 = 'January'
Set @year1 = 2010
Set @month2 = 'January'
Set @year2 = 2012
Set @realmonth1 = Case when @month1 = 'January' then '01' when @month1 = 'February' then '02' when @month1 = 'March' then '03' when @month1 = 'April' then '04' when @month1 = 'May' then '05' when @month1 = 'June' then '06' when @month1 = 'July' then '07' when @month1 = 'August' then '08' when @month1 = 'Sepetember' then '09' when @month1 = 'October' then '10' when @month1 = 'November' then '11' when @month1 = 'December' then '12' end
Set @realmonth2 = Case when @month2 = 'January' then '01' when @month2 = 'February' then '02' when @month2 = 'March' then '03' when @month2 = 'April' then '04' when @month2 = 'May' then '05' when @month2 = 'June' then '06' when @month2 = 'July' then '07' when @month2 = 'August' then '08' when @month2 = 'Sepetember' then '09' when @month2 = 'October' then '10' when @month2 = 'November' then '11' when @month2 = 'December' then '12' end
Set @first = 1
Set @input1 = convert(datetime,(convert(nvarchar(4), @year1) + '-' + convert(nvarchar(2), @realmonth1) + '-' + convert(nvarchar(2), @first)))
Set @input2 = convert(datetime,(convert(nvarchar(4), @year2) + '-' + convert(nvarchar(2), @realmonth2) + '-' + convert(nvarchar(2), @first)))

Select
    ...
Where startdate between @input1 and @input2

Sql Fiddle showing that it works, also tested in MS SQL Server 2005

Upvotes: 1

stb
stb

Reputation: 3513

try using a statement containing month(c.startdate) >= 1 and year(c.startdate) >= 2010. That makes it easier for you

Upvotes: 0

marc_s
marc_s

Reputation: 755541

Why not just use:

SELECT (list of columns)
FROM dbo.YourTable
WHERE c.startDate >= '20100101' AND c.startDate < '20120401'

That just selects all the rows with a startDate between Jan 1, 2010, and before Apr 1, 2012

Upvotes: 2

Related Questions