Hi there,

I am working on one of the job, and in that I need to extract the date from file name ; which I have completed. Date is in this format 20200930 , I need to modify this date to 2020-09-30.

how to add dash in between year , month, and day?


Thank You.

I would most likely use regex replace. Break the string down into its component parts, then put them back together with the dashes. Here we describe the string as 3 groups (a group is surrounded by parentheses) of numbers. Using the number shorthand of \d for a digit and a modifier to indicate how many digits in the group, they can be referenced by the position from left to right using a dollar sign followed by their positional number. So in the "replace with" part of the function, we replace the original string with the first group (4 digits, the year), then a dash, then the second group (the month), then a dash, then the third group (you guessed it, the day).

{REGEX(Replace|20200930|(\d{4})(\d{2})(\d{2})|$1-$2-$3)}

Thanks much, it works!

Note one of the DATEADD functions would work too and may be faster, and easier for future developers that may not know regex. Just add 0 years and specify your format. This just came to me after using DATEADD recently.

{DATEADD(Years|20200930|yyyyMMdd|0|yyyy-MM-dd)}