Filtering Current Month

Question

Hi,

I have a workflow which is based on a ODBC table with transactions from over 5 years. I want to prepare a daily report for the current month.

I have taken out the year and the month from these transaction dates as a filter to take out report for the current month. I applied the filter month and year manually. Is there a way to select the current month and year automatically.

I tried applying DateTimeYear(DateTimeNow()) formulas but i am getting a parse error.

atcodedog05 · Answer

Hi @ashaygnayak

Adding to @RolandSchubert response similar to the above approach.

You can also directly implement it in the SQL query itself that way it will be extracting only required data and not complete data.

Where Year=YEAR(CURDATE()) and Month=MONTH(CURDATE())

You can use cast() if datatype needs to be changed

https://www.w3schools.com/sql/func_mysql_year.asp

https://www.w3schools.com/mysql/func_mysql_month.asp

https://www.w3schools.com/sql/func_sqlserver_cast.asp

Hope this helps : )

RolandSchubert · Answer

Hi @ashaygnayak ,

as @BrandonB already said, it seems to be a  data type problem - DateTimeYear(DateTimeToday()) and DateTimeMonth(DateTimeToday()) both return numeric values, while the fields [Year] and [Month] seem to be of type string.

An additional suggestion: You could use a Dynamic Input tool and select only current month's data from the database - depending on the size of your database table, this could speed up the process.

In your Workflow, you would replace the Data Input tool by the Dynamic Input tool:

The SQL query in the Dynamic Input tool would be like this:

In "Modify SQL Query" you can add your current year/month to the SQL:

Hope this is helpful in any way.

Best,

Roland

atcodedog05 · Answer

Hi @ashaygnayak

Additionally, as @BrandonB mentioned you can also compare the current month on the below way.

ToNumber([Month]) = DateTimeMonth(DateTimeNow())

Hope this helps : )