Query Database Between Month Date Ranges

[Stuugie]

Hi All,

This is something I should know but for whatever reason today it is eluding my mind. I'm trying to query our database to provide me with data between March 2012 and March 2013. Initially, I had the following line:

where a.IPI_Ref_Date between '2012' and GETDATE()

and the output was from Jan-2012 to our most recent April 2013. The information request (IR) I received changed in that the person asking for the IR now wants the data to be from March to March but my statement is getting all messed up because I'm not querying this right. I'd show what I've tried but I don't see a point in offering bad SQL statements. Can someone please advise me so that I can do this right?

[gusano79]

Perhaps something like this will work for you:

DECLARE @enddate DATETIME
SET @enddate = GETDATE() -- or whatever end date you're interested in

SELECT ...
WHERE a.IPI_Ref_Date BETWEEN DATEADD(YEAR, -1, @enddate) AND @enddate

[Stuugie]

Thanks gusano79, I'll give that a go.

[Stuugie]

That was close, however the output is from 2012-07-01 to 2013-04-01. What I need to get is from 2012-03-01 to 2013-03-01. I'm guessing to do this right I need to set @enddate to march of this year so I'm going to try that and get back to this.

[Stuugie]

Yep, that did it

SET @endDate = '2013-03-01'

Thanks so much for this as it is new to me. Now I know for the future.