DateTimeFirstOfMonth() Filter

[DataBlender]

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.

[Joe_Mako · Accepted solution]

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 https://jdunkerley.co.uk/2016/05/15/alteryx-formula-add-ins-v1-1/

Then you can write an expression like:

[Date]<TODATE(DateTimeFirstOfMonth())

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

[SeanAdams]

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