Filter date column with 2 different formats

[ShantanuDagar]

Hello,

I have a date column with dates in 2 different formats. They both can be simultaneously be present in 1 input dataset or might be present 1 at a time. There is also null values possibility.

The date formats or values are: 

16-MAY-12

2015-07-22

2023-04-21

[Null]

For the condition based on requirements I used the filter:

(DateTimeDiff(DateTimeNow(),[Date],"months") >= 6 OR IsNull([Date]))

And this was working pretty well when only 2nd, 3rd, and 4th types were present (yyyy-mm-dd and [Null]) but now with presence of types like 16-MAY-12 those values aren't filtering in.

[dwstada]

the obvious answer is to clean up your date column so you only have one format.

If you can't/don't want to change that column, create a new one with correct formatting and filter on this, then remove it afterwards

[binuacs]

@ShantanuDagar You need to convert the date in to ISO format and appy the filter