How do I use a date range minus 1 year in MSSQL?

Question

I'm creating a query in MSSQL (for SAP Business One) to compare sales and profit on a given date range (using between [date] and [date]) with the sales and profit for the same date range of last year, but I don't know how to do that.

I'm using the following code for the date range:

WHERE t1.docdate between '[%1]' and '[%2]'

thank you

Answer 1

If you want the data for the same date range last year, you have two choices:

WHERE dateadd(year, 1, t1.docdate) between '[%1]' and '[%2]'

or

WHERE t1.docdate between dateadd(year, -1, '[%1]') and dateadd(year, -1, '[%2]')

Notes:

• In the first situation, you are adding a year to the date, not subtracting one.
• The parameters seem strange, but I'm not familiar with SAP formats.
• I strongly agree with Aaron about avoiding between with dates. However, I'm leaving the logic as is because that is how the oP phrased the question.

Answer 2

dateadd(year,-1,[date])

Add and Subtract Dates using DATEADD in SQL Server

Be aware of the implications of using between, as shown here: Bad habits to kick : mis-handling date / range queries - Aaron Bertrand - 2009-10-16