previous month daate

Hi2023

does anyone know how to pull the previous month date data for a report each time it is run?

I am using the SQL Editor in Alteryx

DateTimeFormat(CRG_STAT) >= DateTimeFormat(DateTimeAdd(DateTimeNow(), -1, "month"))

example I found BUT

This one has time inside of it -mine is NOT time just a date.

thanks

Reporting

Accepted answers

Luke_C

Hi @Hi2023

If you're using SQL the Alteryx date syntax won't work. Example SQL syntax:

put your field instead of pdate

<span class="hljs-keyword">SELECT</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">FROM</span>  tablename
<span class="hljs-keyword">WHERE</span> pdate <span class="hljs-operator">>=</span> DATEADD(<span class="hljs-keyword">day</span>,<span class="hljs-number">-30</span>,GETDATE()) 
<span class="hljs-keyword">and</span>   pdate <span class="hljs-operator"><=</span> getdate()

Or use DateDiff

<span class="hljs-keyword">SELECT</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">FROM</span>  tablename 
<span class="hljs-keyword">WHERE</span> DATEDIFF(<span class="hljs-keyword">day</span>,pdate,GETDATE()) <span class="hljs-keyword">between</span> <span class="hljs-number">0</span> <span class="hljs-keyword">and</span> <span class="hljs-number">30</span>

All comments

CatheyH

Have you tried DateTimeToday()? I set the field as "date" rather than "datetime"

Luke_C

Hi @Hi2023

If you're using SQL the Alteryx date syntax won't work. Example SQL syntax:

put your field instead of pdate

<span class="hljs-keyword">SELECT</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">FROM</span>  tablename
<span class="hljs-keyword">WHERE</span> pdate <span class="hljs-operator">>=</span> DATEADD(<span class="hljs-keyword">day</span>,<span class="hljs-number">-30</span>,GETDATE()) 
<span class="hljs-keyword">and</span>   pdate <span class="hljs-operator"><=</span> getdate()

Or use DateDiff

<span class="hljs-keyword">SELECT</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">FROM</span>  tablename 
<span class="hljs-keyword">WHERE</span> DATEDIFF(<span class="hljs-keyword">day</span>,pdate,GETDATE()) <span class="hljs-keyword">between</span> <span class="hljs-number">0</span> <span class="hljs-keyword">and</span> <span class="hljs-number">30</span>

vpogaku

Hi Luke,

I have a similar use case where I always want my date field to calculate till previous month end in my YTD calculations.

We want our YTD logic to work till end of previous month but should not include current month.

For E.g., Even though we don't have current month data (Jan 24) our YTD should work for one prior month of Dec 23 as we won't get the current month Jan 24 values until we reach the end of current month Jan 24.

Thanks, in advance.

vpogaku

Hi Hi2023,

I have a similar use case where I always want my date field to calculate till previous month end in my YTD calculations.

We want our YTD logic to work till end of previous month but should not include current month.

Can you please share your thoughts here?

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280