Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

DateTimeFirstOfMonth() Filter

11 - Bolide

I've just noticed something when using DateTimeFirstOfMonth() that I hadn't seen before.


If I have a filter on a date field with an expression:


[Date] < DateTimeFirstOfMonth()

and my Date is 2017-02-01, first of month is 2017-02-01, I would expect this record to be outputted as false since it is not before that date. However, it is outputted as true.


If I do the same but change my data type to datetime, the record is now outputted as false.


Obviously the datatype is making a difference but I'd expect consistent output between the two.

12 - Quasar

Here are some to consider:


Date data type vs Date data type:

"2017-04-01" < "2017-04-01"

results in False, because they are the same value


DateTime data type vs DateTime data type:

"2017-04-01 00:00:00" < "2017-04-01 00:00:00"

results in False, because they are the same value


Date data type vs DateTime data type:

"2017-04-01" < "2017-04-01 00:00:00"

results in True, because they are not technically the same value. The Date has a Null value for Time, while the DateTime has a value of zero for the Time. If you sort Null vs zero, the Null will be sort as first.


Apparently to Alteryx, a Date with Null Time is less than the same DateTime with a zero for Time.


The function DateTimeFirstOfMonth() will return a DateTime date type value, maybe what we need is a function for DateFirstOfMonth that returns a Date data type value.


Currently all built-in DateTime functions return a DateTime data type, and I do not know of a built-in function to convert a DateTime to a Date data type.


Although thanks to @jdunkerley79 we can do what you need with his Alteryx Formula Add Ins at


Then you can write an expression like:




and it will behave exactly how you expect it to because it is comparing a Date data type vs a Date data type.


17 - Castor
17 - Castor
@Joe_Mako 's response is tremendously comprehensive - only thing I'd add is that you can always cheat a bit when working with dates:

If you have a date-time that you're comparing to a date - and you format the date in YYYY-MM-DD format, then they sort perfectly as strings.
So convert both to strings in format YYYY-MM-DD; and then trim both back to 10 characters and then this will do the trick too.

BTW - this is also a sneaky way to get the first of the major date-time periods too in any platform (we used to do this a lot in SQL) - for example, to get the first of a month, just trim the same date back to 8 characters (YYYY-MM-) and then add "01" on the end (to get YYYY-MM-01), and then you have a cheap and quick way to get the date on the first of any month.