Solved

Select - past 3 months

Posted on 2011-09-12
4
284 Views
Last Modified: 2012-06-21
I have a column called "Shippeddate" and it is datetime. I want to get the past three months only.
Do we use datepart?
0
Comment
Question by:VBdotnet2005
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 63 total points
ID: 36525430
You can use DATEPART; however, I would not recommend that. I would instead, do something like this:

Shippeddate >= DATEADD(MONTH, -3, GETDATE())

Based on how I need the data, I might tweak from there, but I would start with that.
0
 
LVL 32

Assisted Solution

by:Ephraim Wangoya
Ephraim Wangoya earned 62 total points
ID: 36526565
consider getting the first day of the month then subtracting three months

where Shippeddate > DATEADD(MM, -3, DATEADD(DD, -(DAY(GETDATE())-1), GETDATE()))
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 36526572
You could remove the time part as well
declare @requireddate datetime

set @requireddate = DATEADD(DD, 0, DATEDIFF(DD, 0, GETDATE()))
set @requireddate = DATEADD(MM, -3, DATEADD(DD, -(DAY(@requireddate)-1), @requireddate))

.....
where Shippeddate >= @requireddate

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36526630
If you want to go back to the first day of the month three months ago, you can do that like this:

WHERE ShippedDate >= DATEADD(MM, DATEDIFF(MM, 0, GETDATE())-3, 0)

It strips the time and gets you to the first day of the month at the same time.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

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

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

Join & Ask a Question