• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 46
  • Last Modified:

Search the data of actual month and the two previous months

hello ,
I need to search the data related the actual month and the two previous months
I use the below SQL request:
WHERE datepart(YYYY,dTime)=
datepart(YYYY,GETDATE( )) and datepart(m,dTime) in (datepart(m,GETDATE( )),datepart(m,(dateadd(m,-1,GETDATE( )))),datepart(m,(dateadd(m,-2,GETDATE( )))));
this request work fine , but when I’m in January i don’t have the data .. and this normal
because I use datepart(YYYY,GETDATE( ))  … and I don’t found the good request ..
can someone help me plz.
Thanks
0
mell lian90
Asked:
mell lian90
  • 3
  • 2
  • 2
1 Solution
 
Vaibhav GoelMSBI , SQL ConsultantCommented:
Hello Mell Lian90
I need to search the data related the actual month and the two previous months

WHERE
      dTime >= DATEADD(MONTH,-2,DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))
      AND dTime < SELECT DATEADD(MONTH,1,DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))
0
 
Lee SavidgeCommented:
Are you looking to find records where the date is between NOW and last month and month prior to that?

So for today you'd want all records for December, November, and October?

The field that holds the date in question is dTime?
0
 
Lee SavidgeCommented:
If so, this works:

declare @d datetime

select @d = cast('28 feb 2017' as datetime)

select @d,
       dateadd(m, -2, @d)-datepart(d, @d)+1



select * from myTable where
dTime <= @d and dTime >= dateadd(m, -2, @d)-datepart(d, @d)+1

Open in new window


Avoid using BETWEEN.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
mell lian90Author Commented:
@Lee Savidge  ,

yes exactly ..
0
 
Vaibhav GoelMSBI , SQL ConsultantCommented:
Hello Mell Lian90

I have also done the same thing. Please try my solution also.  

Thank you

Vaibhav
0
 
mell lian90Author Commented:
@ Vaibhav Goel  , thanks for your answer , it's exactly what i need
0
 
mell lian90Author Commented:
the best solution
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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