Filtering Current Month

[ashaygnayak]

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. 

[BrandonB]

Your parse error could be due to data type differences. You are correct that DateTimeYear(DateTimeNow()) will return 2021. Maybe wrap this with a ToString(), or wrap your column with ToNumber()

For example

ToNumber([Year]) = DateTimeYear(DateTimeNow()) 

in a filter tool

[atcodedog05]

Hi @ashaygnayak 

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

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

Hope this helps : )

[RolandSchubert]

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]

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 : )