Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Expression for the previous 3 months in a sql table

Soxfaninfl
8 - Asteroid

Hi Everyone,

 

I need an expression to query the previous three months in a sql table in an input tool. I don't need rolling 3 months. So for example, if I run the workflow on 5/20/19 I get the data for 2/1/19 to 4/30/2019 with the correct expression that I am looking for. I need to be able to run on any day of the current month of May and still be able to get data for  2/1/19 to 4/30/2019.

 

Thanks,

 

mackedet

6 REPLIES 6
Thableaus
17 - Castor
17 - Castor

Hi @Soxfaninfl 

 

If it's a query in SQL Server, I'd use this where clause:

 

WHERE  DATEADD(mm, -3, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) <= YOURDATEFIELD AND DATEADD(DAY, -(DAY(GETDATE())), GETDATE()) >= YOURDATEFIELD

 

Cheers,

BrandonB
Alteryx
Alteryx

Hi @Soxfaninfl 

 

The following formulas should do the trick. Workflow is also attached. 

 

date formulas.png

afv2688
16 - Nebula
16 - Nebula

Hi @Soxfaninfl ,

 

For your calculations:

From

todate(tostring(tostring(DateTimeYear([DateTimeNow])) + "-0" + tostring(DateTimeMonth([DateTimeNow])-3)) + "-" + tostring(DateTimeDay([DateTimeNow])))

 To

DateTimeParse(DateTimeAdd([DateTimeNow],-DateTimeDay(DateTimeNow()),"days"),"%Y-%m-%d")

Untitled2.png

Soxfaninfl
8 - Asteroid

Hi Thableaus,


Thank you for responding. I'm getting the following error. The field is a date/time field. Please advise.

 

Error Message.jpg

 

Thableaus
17 - Castor
17 - Castor

@Soxfaninfl 

 

I think there was a missing parenthesis:

 

WHERE 

DATEADD(mm, -3, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))

<= YOURDATEFIELD

AND DATEADD(DAY, -(DAY(GETDATE())), GETDATE())

>= YOURDATEFIELD

Soxfaninfl
8 - Asteroid

Thank you, Thableaus! This worked perfectly.

Labels