asked on

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

ASKER CERTIFIED SOLUTION

Vaibhav Goel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lee

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?

Lee

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.

mell lian90

ASKER

@Lee Savidge ,

yes exactly ..

Vaibhav Goel

Hello Mell Lian90

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

Thank you

Vaibhav

mell lian90

ASKER

@ Vaibhav Goel , thanks for your answer , it's exactly what i need

mell lian90

ASKER

the best solution