I need to load via a canonical what I thought was a date but in fact turns out to be a string (non-standard date format from the customer). so somehow I need to stitch the date together manually from pieces of the string (right after I find a pencil and sick it in my eye!).
I found an example of this in the a customer transformation file TransformCanonical****BillingInformationToDetailedBill. (customer name hidden )
….~ expression “concat(substring(billStartDate,0,4),‘-’,substring(billStartDate,4,6) …….
This seems needlessly ugly!!!
Is there some mask I can provide to convert the string in the canonical to a datetime (Oracle SQL equivalent would be TO_DATE(‘2018-JAN-17-19:03:45’,‘YYYY-MON-DD-HH24:MI:SS’)?
Please let me know if there is an elegant way to do this instead of the string gymnastics approach I see in the transform above.
If this format mask solution does not exist, it probably should?!