I have a file named Name_Excaa_2021-11-28.xlsx
and I want to extract only the date value 2021-11-28 using a derived column.
I am using the expression below, but it gives me the output 2021-11-28.xlsx
RIGHT((DT_STR,50,1252)@[User::FileName],FINDSTRING(REVERSE(@[User::FileName]),"_",1) - 1)
How can I solve this and get the date value 2021-11-28
Thanks in advance.
Assuming that your file names always end with the format yyyy-mm-dd.xlsx
you can just select that part using right
to grab the last 15 characters and then extract just the 10 characters of the date from those 15 characters using left
:
left(right(@[User::FileName],15),10)