• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 457
  • Last Modified:

Go back 6 months in sql server

Hi all.

I want to be able to go back exactly 6 months from my current month in my orderdate field of myTableA. I've tried using
DATEADD(month, -6, GetDate())

But it'll pull records starting from let's say 03/20/2011 till today. But I want it to include ALL days in March so orderdates starting 03/01/2011 until today. I want to get records from the entire month, basically ignore the day and just look at the month (Sept, Aug, July, June, May, April and March). And if the current month is February 2012 then (Feb 2012, Jan 2012, Dec 2011, Nov, etc)

Any idea how I can do this?

            
0
printmedia
Asked:
printmedia
  • 4
  • 2
1 Solution
 
Patrick MatthewsCommented:
Try something like this:

SELECT *
FROM SomeTable
WHERE DateTimeColumn >= DATEADD(month, DATEDIFF(month, '2000-01-01', GETDATE()) - 6, '2000-01-01') AND
    DateTimeColumn < DATEADD(month, DATEDIFF(month, '2000-01-01', GETDATE()) - 5, '2000-01-01')

Open in new window

0
 
printmediaAuthor Commented:
Thanks for the quick reply!

Would you mind explaining the code, I want to make sure I understand it.
0
 
Patrick MatthewsCommented:
Certainly.

DATEDIFF(month, '2000-01-01', GETDATE()) tells us how many month boundaries there are between 1 Jan 2000 (nothing magical about that date; any Jan 1 will do) and the current date.  By putting that back into DATEADD using the same base date of 1 Jan 2000 we can get the beginning of the current month:

DATEADD(month, DATEDIFF(month, '2000-01-01', GETDATE()), '2000-01-01')

Of course, since you want the first of the month from 6 months ago, we have to modify that a bit:

DATEADD(month, DATEDIFF(month, '2000-01-01', GETDATE()) - 6, '2000-01-01')

Then that last expression gives us the first day of the month from 5 months ago.

Be sure to have a look at this article by EE superstar and Microsoft MVP angelIII, especially Section 6, "Date Ranges":

http://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don't-be-scared-and-do-it-right-the-first-time.html
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
Alpesh PatelAssistant ConsultantCommented:
Select * From Table wher Date Between DateAdd(month-6,Getdate()) AND Getdate()
0
 
Patrick MatthewsCommented:
PatelAlpesh,

Did you even read the question?  The Asker indicated that s/he had already tried and rejected that approach, because s/he needed an entire month.

Patrick
0
 
printmediaAuthor Commented:
Thanks matthews!

I changed your code a bit because I wanted to include the current month as well.
WHERE DateTimeColumn >= DATEADD(month, DATEDIFF(month, '2000-01-01', GETDATE()) - 6, '2000-01-01') AND DateTimeColumn <= GetDate()

Open in new window

0
 
Patrick MatthewsCommented:
printmedia,

OK, I see now--I thought you wanted just that single month :)

If there is any possibility that your table contains items for future datetimes still scheduled to occur this month, and you want to include those items:

WHERE DateTimeColumn >= DATEADD(month, DATEDIFF(month, '2000-01-01', GETDATE()) - 6, '2000-01-01') AND 
    DateTimeColumn < DATEADD(month, DATEDIFF(month, '2000-01-01', GETDATE()) + 1, '2000-01-01')

Open in new window


Patrick
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now