Cant get a date filter to work

[craigja]

I have a yxdb file with month end valuation dates in it, the field is a date field (i.e. not datetime).  I want to filter for the last day of the previous month, if I drop a filter onto the canvas and select the 31st of August 2023, I get data.  If I replace the static date with this it returns no results:

DateTimeAdd(DateTimeTrim(DateTimeToday(),'FirstOfMonth'),-1,'day')

I have added a new field to the dataset with a formula and added in the above date formula, then added a filter and said to filter when Valuation Date = My New Date Field - I get results!  Why when I use the DateTimeAdd(DateTimeTrim(DateTimeToday(),'FirstOfMonth'),-1,'day') formula in a filter does it not work?

[DataNath · Accepted solution]

Hey @craigja, if you wrap your expression in ToDate(), does that help? I.e:

ToDate(DateTimeAdd(DateTimeTrim(DateTimeToday(),'FirstOfMonth'),-1,'day'))

[craigja]

Yes!  Was just coming back to this page to say wrapping it in ToDate solved the issue - any ideas why I need to do this?

[Prometheus]

@craigja Your original date field was String data type. DateTimeAdd, DateTimeTrim, and DateTimeToday are Date data type functions. Wrapping it in "ToDate" changes the data type from String to Date. 

[DataNath]

Using DateTime...() functions casts the field as a DateTime data type, defaulting to midnight and so in your example, you'd be comparing 2023-09-15 to 2023-09-15 00:00:00. Even though they are the same, because of the time element they're not exactly the same in terms of how Alteryx sees it. ToDate() therefore just casts it back to a standard date so that they can be compared like-for-like again.

[FinnCharlton]

@craigja @Prometheus It's not to do with strings, but to do with the Date and DateTime data types. The DateTime functions you are using return DateTime values, which do not return as equal to Date values, even if the date is the same. Wrapping in ToDate() means you are now comparing two Date types, which do return as equal to each other.