Posted by Hugo Kornelis on 05/11/07 20:49

On 10 May 2007 15:22:08 -0700, --CELKO-- wrote:

>>> I know how to establish the last day of the previous month part, I'm just not sure of how best to design the remainder of the query. <<
>
>Instead of using procedural coding, why not use a table of the
>reporting periods for a decade or two? A simple BETWEEN predicate
>will classify each sale quickly and give you extra control over non-
>operating days, etc.

Hi Joe,

When has using a call to a standard function in a query started to be
"procedural coding"?

Using a table of periods is a great technique, especially when there are
exceptions. But in situations without exceptions, using builtin
functions is far faster than joining to a secondary table.

For the original question in this thread, the best and fastest technique
is:

WHERE solddate >= DATEADD(month,
DATEDIFF(month,'20000101',CURRENT_TIMESTAMP),
'19990101')
AND solddate < DATEADD(month,
DATEDIFF(month,'20000101',CURRENT_TIMESTAMP),
'20000101')

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

[Back to original message]