Filter in db for dates from today until next three months

Question

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 Answer

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 · Accepted Answer

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!