Find 7 months from current Date

Question

Please help!

I am pulling data from sql and it is a large amount. I only need 7 months from the current date (28 weeks) but there is not a date field only month and year. Is there a way to pull this information within the sql query in the input tool?

alexnajm · Accepted Answer

Then just calculate a new Date column based on your month and year! DateTimeFormat in a Formula tool or the DateTime tool should do the job: https://help.alteryx.com/current/en/designer/functions/datetime-functions.html#datetimeformat

jrlindem · Accepted Answer

To @alexnajm's point, if you do have a [Month] and [Year] field then you could just "spoof" the day by always using the 1st to create a date field:  ToString([Year]) + "-" + ToString([Month]) + "-01" and then treat that as a Data Type = Date.

Once you've got that then you can apply the same DateTimeAdd function that Alex mentions above to create your logic.

But knowing that you have a date and year field is all you would really need.  The article that he posted is worth a review regardless as it will open up more possibilities to solve date related needs.

-Jay

abe_ibanez · Accepted Answer

Hello,

I would use the system date within your query. If the database does not have a date field, you can extract the month and year from the system date and use that for your WHERE statements. 
For example, in Oracle, I would use EXTRACT(YEAR FROM SYSDATE) and EXTRACT(MONTH FROM SYSDATE).

So to get your the values for your filters, you can use:

YEAR>= EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE, -7))

AND MONTH >=  EXTRACT(MONTH FROM ADD_MONTHS(SYSDATE, -7))

If your month is saved as the in a different format, you can also use TO_CHAR(ADD_MONTHS(SYSDATE, -7), 'format')