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

I'm relatively new to SQL queries and need to automate a report that will mail on the first of the month and send info for last calendar month. So using the last 30 days is insufficient. Currently we have the report created with a prompt for dates. But I need to automate it so its not an option. Its for SAP Business one 9.1 with SQL.

 Basically I need to replace this portion of an existing query below. to give last calendar months date range.

WHERE (%other logic%) and CONVERT(nchar(8), T0.DocDate, 112) >= [%0] AND  CONVERT(nchar(8), T0.DocDate, 112) <= [%1]
Thomas_L_MTLAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Randy PetersonCommented:
This will get you the first of last month: CONVERT(DATE,SUBSTRING(CONVERT(VARCHAR, DATEADD(m, -1,GETDATE()),112),1,6)+'01' )

While, this get's you the last day of the previous month: DATEADD(d,-1,SUBSTRING(CONVERT(VARCHAR, GETDATE(),112),1,6)+'01')

Although I would recommend you doing this actually.

and 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')

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
Yikes. Please dont convert datetime data into nchar or nvarchar for this style of need. You get way better performance by sticking with date/time.
 
Do not use BETWEEN for date range filters either

DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -1, 0) -- first of last month

DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) , 0) -- first of this month


Then filter like this

Where to.docdate >= {first of last month}
And to.docdate < {first of this month}

Using this approach you avoid lots of costly data conversions and get access to indexes on those date columns.
for more on this topic please see: "Beware of Between"
PortletPaulEE Topic AdvisorCommented:
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
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

RayData AnalystCommented:
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
Between '07/01/2015' and '07/31/2015'  
 

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 to.docdate >= '20150701' and to.docdate < '20150801'

where you now have a date range with two different looking months.
PortletPaulEE Topic AdvisorCommented:
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
RayData AnalystCommented:
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.
PortletPaulEE Topic AdvisorCommented:
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
PortletPaulEE Topic AdvisorCommented:
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)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SAP

From novice to tech pro — start learning today.