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?
