How set a date range for LAST month in automated reporting (SBO / SQL)

the best practice with date and time ranges is to avoid BETWEEN and to always use the form:

WHERE col >= '20120101' AND col < '20120201'
This form works with all types and all precisions, regardless of whether the time part is applicable

.
http://sqlmag.com/t-sql/t-sql-best-practices-part-2
Itzik Ben-Gan

Please take this as a friendly warning. If you use an approach that is based on between there is a risk of incorrect results.

Where to.docdate between '20150701' and
.'20150801'

That Includes data with date of 2015-08-01

But the following includes all of july and only july

Where to.docdate >= '20150701'
and to.docdate < '20150801'

Avoid using beteeen for date range filtering

I realize this is a closed question, but couldn't help myself ....

Performance aside, between is easy to use and makes visual sense to many.

for example
 

this clearly shows that it is a calendar month when looking visually and will get results from ANY date in July 2015.  

Much clearer looking than  

where you now have a date range with two different looking months.

This is a matter of accuracy

the following is WRONG, it will produce results that are 24 hours short of the full month

          Between '07/01/2015' and '07/31/2015'  

the following is also WRONG, it includes data from August when you expect only July

T0.DocDate BETWEEN CONVERT(DATE,SUBSTRING(CONVERT(VARCHAR, DATEADD(m, -1,GETDATE()),112),1,6)+'01' ) AND CONVERT(DATE,SUBSTRING(CONVERT(VARCHAR, GETDATE(),112),1,6)+'01')

Between is EVIL and if you don't believe me try these:

"Bad habits to kick : mis-handling date / range queries"

"What do BETWEEN and the devil have in common?"

The ultimate guide to the datetime datatypes

Paul, I didn't say it was better nor did I say it was your way. I simply said its easier to visually "get".
Also my previous was NOT WRONG as my example was based on a date not date/time.  

Again for clarity, I'm not arguing with your original statements, only saying that one way is easier for some to "see" and still functional when used properly.

Any use of an sql statement generally suggests that the author knows what they are getting (as I do with a between).  If you don't understand what you are getting when you write the code in the first place, well problems will persist.

Ray, I appreciate you are trying to help. I will continue however to disagree that using between is in fact helpful in any context for date ranges.

 if you place the condition that
                   [somecolumn] Between '07/01/2015' and '07/31/2015'  

is for date columns only (i.e. not for datetime, smalldatetime, datetime2)  then and only then will it produce an accurate outcome.

The fact is however that Between '07/01/2015' and '07/31/2015' remains 24 hours short of a full month. The human mind copes with this by assuming something along these lines:

[somecolumn] (is) between (the beginning of) '07/01/2015' and (the end of) '07/31/2015'

but SQL isn't the same it does NOT understand (the end of) '07/31/2015' and in fact this is exactly how SQL interprets it:

[somecolumn] (is) greater than or equal to (the beginning of) '07/01/2015'
                      and less than or equal to (the beginning of) '07/31/2015'

so, instead of going to "the end of" for the second date, it does the reverse and uses "the beginning of" PLUS it INCLUDES BOTH points in the outcome by using equals at both ends.

<<sigh>>

====
The choice is rather simple I believe.

use between = unreliable, contentious, buggy, ambiguous technique

avoid using between = strong, reliable, accurate and consistent technique & = "best practice"

can be used in every rdbms
can be used regardless of the data type precision (nanoseconds, milliseconds, whole date, etc)
is not affected by rounding (due to lack of precision in data type)

                  [somecolumn] <= '07/01/2015' and [somecolumn] < '08/01/2015'  

Yes, it may appear odd, and maybe a little harder to "get"
but I would rather have "reliably accurate results" over "clean look"

{+several edits}
sorry, was dealing with mobile phone keyboards and fat fingers

one further thing, & I promise my last

SQL Server has only 2 "safe" date literal styles

YYYYMMDD
or
YYYY-MM-DDThh:mm:ss[.mssss]

I have used '07/01/2015' and  '07/31/2015' to focus on between & "between is evil"

so, while I adopted the American sequence of month/day/year above, it isn't recommended practice

this is:

                  [somecolumn] <= '20150701' and [somecolumn] < '20150801'  

which may be even slightly harder to "get" (but like mum's medicine, it's good for you)