Solved

# Get past 2 months full data and current month data up to now

Posted on 2013-01-17
372 Views
I need to run a query that gets the prior 2 months of data and then then current month's data up to the current date. When the current month ends, I need it to tic forward and continue the cycle. So, on January 17th, I would want all of November, all of December and January until now (the 17th). On February 1st, I would want all of December, all of January and February up to the 1st, and so on.
0
Question by:LCNW

LVL 22

Expert Comment

ID: 38789234
Here's some code to get you First of this month, last month, the month before that and Today:

``````DECLARE @Today       DATETIME,
@FirstThis   DATETIME,
@FirstLast   DATETIME,
@FirstTwo    DATETIME

SELECT @Today = dateadd(dd, datediff(dd, 0, getdate())+0, 0)     -- Today's Date, midnight past
SELECT @FirstLast = DATEADD(mm, -1, @FirstThis)
SELECT @FirstTwo  = DATEADD(mm, -2, @FirstThis)

Print @FirstThis
Print @FirstLast
Print @FirstTwo
Print @Today

Output:

Jan  1 2013 12:00AM
Dec  1 2012 12:00AM
Nov  1 2012 12:00AM
Jan 17 2013 12:00AM
``````

You could then run your query where dates are between @FirstTwo & @Today

Be careful if you've got times in your date fields since my @Today is midnight at the start of today in this example.

If you wanted Midnight of the first two months back up to current, just set today to @getdate()
0

LVL 42

Accepted Solution

zephyr_hex earned 500 total points
ID: 38789241
i assume you have a datefield you'll be comparing to.

where
DateField>= dateadd(month, datediff(month, 0, getdate()) - 2, 0)  //this is Oct 1, 2012
and DateField <= getdate()   //this is today, Jan 17, 2013
0

LVL 1

Author Closing Comment

ID: 38789271
Spot on, thank you.
0

## Join & Write a Comment Already a member? Login.

### Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

#### 747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!