>>>>If you're using SQL 2012 and up and can guarantee that, then I suggest to try my version as simpler one.
>>>
>>>I can't be sure that the user won't be using 2008
>>
>>With datetime values you better use grater or equal than and less approach. With dates between is perfectly acceptable.
>
>Much of my work is with accounting applications, where in many cases the month and year, and not the day, are significant.
>
>In those cases, rather than prompting for a date, I prompt the user for the month and year and do my queries this way:
>
>
>SELECT * FROM view_expandedcoindrop WHERE MONTH(dropdate) = @rptmonth AND YEAR(dropdate) = @rptyear ORDER BY store,dropdate
>
>
>
>The users prefer that to having to enter "10/31/16" when the 31 is superflous and meaningless.
>
>I couldn't figure out a good way to get a range just using month and year.
Just curious - when you look at the execution plan for that query, what do you get?
Roughly how big is this invoice table? (# of rows)