Re: Selecting Quarter and Year Parameter

On Fri, 7 Dec 2007 06:37:00 -0800, Ryan Mcbee wrote:

I am working on a report where the end user would like to say giving me data
for quarter 1 of 2007,ect. The data in my table has the year and date fields.

Hi Ryan,

Huh? Year AND date fields? Isn't that a bit redundant, since the year is
part of the date? Try to see if there is any good reason for this
duplication, otherwise get rid of the Year column.

How do I begin to start this query? I know I can say @Year for the year
parameter, but I am not sure how to get it to return a particular quarters
worth of data.

DECLARE @Year int,
@Quarter tinyint;
SET @Year = 2007;
SET @Quarter = 1;

DECLARE @StartRange datetime,
@EndRange datetime;
SET @StartRange = DATEADD(year,
@Year - 2000,
DATEADD(quarter, @Quarter - 1, '20000101'));
SET @EndRange = DATEADD(quarter, 1, @StartRange);

SELECT ...
FROM ...
WHERE YourDate >= @StartRange
AND YourDate M @EndRange;

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
.