DateTime using DateDiff

Hello everyone.

Im currently using the DateDiff function to filter my DateTime columns but am finding it somewhat troublesome. Currently I am having to write the same select statement 3 times if I want to filter by month, year or all (ignoring dates).

To find @.PurchaseTotal for the year, I have to write the following:

SELECT
 @.PurchaseTotal = Sum(PurchaseTotal)
FROM
_Expenses
WHERE
 DateDiff(yyyy, DateOf, @.IntervalDate) = @.Interval

To find @.PurchaseTotal for a month, I have to write the following:

SELECT
 @.PurchaseTotal = Sum(PurchaseTotal)
FROM
_Expenses
WHERE
 DateDiff(mm, DateOf, @.IntervalDate) = @.Interval

To find @.PurchaseTotal for all the records, I have to write the following:

SELECT
 @.PurchaseTotal = Sum(PurchaseTotal)
FROM
_Expenses

I've tried the following code but I get an error.

DateDiff(@.DateParameter, DateOf, @.IntervalDate) = @.Interval

Error says something like "incorrect parameter 1 for DateDiff."

It seems you have to write a different select statement for month, day and year. Also If you want a total from all the records you have to write yet another select statement.

Does anyone know of a DateTime function that allows parameters to specify for month, day and Year? Also does anyone know of a DateTime function that works like the COALESCE function to where you can send it a NULL value and give you all the records?

Thank you ahead for any direction you can give.

AlecGratulations for choosingthe worst possible approach.

::WHERE DateDiff(mm, DateOf, @.IntervalDate) = @.Interval

Means, in SQL Server language: DO NOT USE AN INDEX.

Why do you not go the easy way?

::WHERE DateOf BETWEEN @.StartDate and @.EndDate

which is WAY less processing for SQL Server, allows it to use an index and in general is faster?