Converting string to datetime in Transform


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?!



You can use the expression engine function dateTime(time, format)
Example: dateTime(“2010-10-10T12:00:00-08:00”), dateTime(“2007-05-24 00:00-06:00”, “yyyy-MM-dd HH:mmZ”)

For more information on functions to use in Transform, please check c3ShowType(ExpressionEngineFunction)


Help updating multiple Meta fields with a ternary?

I was able to find an example like this:

day : ~ expression "dateTime('2014-01-01', 'yyyy-MM-dd') + (day > 0 ? period(day,'DAY') : null)"

And this documentation with c3ShowType(ExpressionEngineFunction), anytime you want to know what can be done in an expression, check the type “ExpressionEngineFunction”

Returns a JODA datetime object (default format is ISOdatetimeFormat). Allows specifying an optional format for parsing the input. The format must be JODA datetimeFormat compatible.

Refer datetimeFormat

Example: dateTime("2010-10-10T12:00:00-08:00"), dateTime("2007-05-24 00:00-06:00", "yyyy-MM-dd HH:mmZ")

time: string required
string input of time in ISOdatetimeFormat

format: string
JODA datetimeFormat

Returns datetime required
a JODA datetime object (default format is ISOdatetimeFormat).

Declaration show```

How can I apply an offset to a timestamp field in a transform?

Thanks so much guys. I found the information in the expression Engine as you suggested. Will be implementing this as part of my efforts.



To add to the already great answers, I’ve found this URL very helpful for knowing what parameters can be used to construct the mask:

1 Like