Filter in db for dates from today until next three months

[Amadeu_gustavo]

Hello there designers,

I have a db with with a ton of records  with past and future dates (format 'yyyy-MM-dd') and I need to filter two different periods (in different workflows) :

• First I need the records of the current month and the next three months.

Today is 2020-07-20,

I need the records of 2020-07-01 until 2020-10-30;

• Also I need the records of today until past three months

Today is 2020-07-20

I need the records of 2020-07-20 until 2020-10-20 (or 2020-10-22 because of the months with 31 days). 

I know how to do it processing after Data Stream Out, but since the db is too big, doing in db tools will save me a big time.

Thanks since now

[RolandSchubert · Accepted solution]

Hi @Amadeu_gustavo ,

you can use a where clause doing a bit of time calculation in your SQL, for MS SQL it should be something like

WHERE  [Date] >= GetDate() AND [Date] <= DateAdd(month, 3, GetDate())

for the next three months,

WHERE  [Date] <= GetDate() AND [Date] >= DateAdd(month, -3, GetDate())

for the previous three months.

Let me know if it works for you.

Best,

Roland

[Amadeu_gustavo]

for the statement where I get the next three running months it worked.

But for the second statement I need to filter from first  of the month (2020-07-01) until next three months (until 2020-09-30 or 2020-10-01).

[Amadeu_gustavo · Accepted solution]

I was able to get the first date of the month with the sentence:

Case
	when datepart(month,getdate()) < '10'
	then 
		cast(datepart(year,getdate())||'0'||datepart(month,getdate())||'01'as date)
	else
		cast(datepart(year,getdate())||datepart(month,getdate())||'01' as date)
end

now I can apply the 'dateadd' and filter. Thanks for the help!