Parse String "dd.mm.yyyy" to Date

Hello !

We have a customfield in projectrack (formerly Profields) that is a string used to store date due to synchronisation process with external tool.

We need to use this field as a Measure date on Eazybi in order to display a Gantt.
The current format is “dd.mm-yyyy”, for instance “01.05.2021” stands for 1st of may 2021.

I was trying to create a custom measure, using dateParse on the existing string measure. but is not working as expected.
Do you have any advice on how we could parse this string in Date ?
Thanks a lot.

Hi @pchampet,
Function DateParse() expects date in specific format, yyyy-dd-mm or mmm dd yyyy.

You might want to decompose and compose the string to match yyyy-dd-mm format and then convert it to the date type. Use string transformation functions Right() and ExtractString() and concatenation || for rearranging order of parts of string

--reorganize string to match format yyyy-mm-dd
DateParse(
  --year is last 4 values
  Right([Measures].[string date format],4) ||"-"||
  --month is first group of numbers after dot
  ExtractString([Measures].[string date format],'.*\.(\d+).*',1) ||"-"||
  --day is first group of numbers
  ExtractString([Measures].[string date format],"(\d+)",1)
)

After those transformations, you may set measure formatting to Date / Time.
More details on used functions described in the documentation: MDX function reference.

Best,
Zane / support@eazyBI.com

I had to change the second part for extracting the months.
But this is definitevely what I was looking for.
Thanks a lot !

1 Like