Regex parse Date from Filename

Question

Hi,

I am pretty new in regular expression and been having trouble understanding it. I want to parse the date from a file name.

For example:

File name : FFTT INC Created_2023_02_01

Date format in filename: yyyy_mm_dd

I want to extract the date so I can sort the files by date in alteryx for dynamic input. I have tried sorting with creation date but I have some reports which have been created on same dates but the data is for another date. Hence this request.

Any help is greatly appreciated. Thanks

Nithin

ShankerV · Accepted Answer

Hi @Nithinpanicker

(\d{4}_\d{2}_\d{2})

Please find the expected output.

Felipe_Ribeir0 · Accepted Answer

Hi @Nithinpanicker

One way of doing this:

DateTimeParse(REGEX_Replace([File Name], '(.*)(\d{4}_\d{2}_\d{2})(.*)', '$2'), '%Y_%m_%d')

DataNath · Accepted Answer

Realised after posting initially that we don't even need to mess around with date functions etc as we're literally just going from YYYY_MM_DD to YYYY-MM-DD and so can simplify this even further with a formula along these lines. The output date type being set to a date will allow you to sort properly: