Filtering on first of month

[PuffinPanic]

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

[mbarone · Accepted solution]

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

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

[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.  

Had to look back in my notes to see the details.

[mbarone]

Changing to datetime in the formula adds in the extra HH:MM:SS:

And yields only the 38 records after the filter:

But the left10:

Puts both dates on an even level and will yield the 40 records, even if the first of month is date/time.

[atcodedog05]

Hi @PuffinPanic 

Here is my theory on it. It seems like when date is compared in filter its comparing them as strings. This way "2021-09-01" is lesser than "2021-09-01 00:00:00". Hence even if they are same dates just date is lesser than datetime. This also explains the usage of left() function.

Here is a workflow where i am checking sort order. It maybe this might be the case.

Thank you @PuffinPanic  for sharing this usecase. It gave me an opportunity to explore on few concepts 🙂

Hope this helps : )

[PuffinPanic]

Thank you for all the great responses. It looks like this is the trickiness was in the datetime as suggested and I have this working now.

PuffinPanic

[mbarone]

Welcome, glad you got it working!

[atcodedog05]

Thank you for sharing the usecase 🙂 @PuffinPanic 

It definitely a learning curve for me 😀