Filtering on first of month

Hi All,

Not sure where I'm going wrong with this one, and I can't find an answer, so I'm putting it out there in the hope that someone can help. I'm thinking it must be user error, but I can't figure out why.

I'm trying to filter entries for the current month out of a larger list using [Creation Date] >= DateTimeFirstOfMonth() which should, I think, give me every date from 1/sept/2021 to today (20/sept/2021), but it is excluding all entries from 1/sept/2021 and only including from 2/sept/2021 onwards. For the record, I also tried [Creation Date] = DateTimeFirstOfMonth() which got no results.

When I have tried putting DateTimeFirstOfMonth() into a field then filtering on the field, I get all of the records ([Creation Date] >= [first of month])

Can anyone tell me why I get different results from the two filters please? I'm happy to use the 2nd option if it needs to be done this way, I'm mostly wondering why the first doesn't work for my own information.

I hope I have managed to attach the workflow (first time doing this!)

Thanks!

PuffinPanic

filter from first of month.yxmd

Developer Tools

Date Time

Preparation

Accepted answers

mbarone

Try "Left(DateTimeFirstOfMonth(),10)".

If you're using a Filter Tool, by default, since it's a "DateTime" function, it will compare minuets/hours too and you'll miss the last minute or something like that (happened to me too a long time ago and ever since I just use left 10).

All comments

mbarone

Try "Left(DateTimeFirstOfMonth(),10)".

atcodedog05

Hi @PuffinPanic

Even i am curious on this. I have a similar conclusion as @mbarone when we change data type of first of month to datetime. Both are showing results from 2021-09-02. Refer below. I am looking more into this i will let you know of i find anything.

When i convert the creation date to datetime it seems to work as expected for 2nd branch. Interesting and strange indeed.

Hope this helps : )

filter from first of month.yxmd

mbarone

It's definitely that. I had the same issue a long time ago and spoke in detail about this with my rep at the time. In the formula tool, you can designate the field type as Date. So the Filter Tool sees "Creation Date" as a Date field. No issues. When comparing it will compare YYYY-MM-DD for Creation Date. If you put a select tool after the Text Tool in the workflow, you'll see it also interprets it as Date.

But, with the DateTimeFirstOfMonth() function, the filter tool interprets it as DateTime and adds HH:MM:SS to the format. Using Left10 on that function will get to work as expected.