Converting string to datetime in Transform

#1

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

0 Likes

#2

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)

0 Likes

Help updating multiple Meta fields with a ternary?
#3

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")

Parameters:
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```
0 Likes

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

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

0 Likes

#5

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:

http://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html

1 Like