Use case: to convert DateTime field (copy from Salesforce) to Date field, or Text field in Einstein Analytics.
In this blog, I'll add new fields in Dataflow using computeExpression. We will use a DateTime field with API name: Submitted_Date_Time__c
formula: substr(Submitted_Date_Time__c,1,10)
This formula will take the first 10 characters.
Original: 2017-05-03T09:43:28.000Z --> 2017-05-03
Date
formula: toDate(Submitted_Date_Time__c_sec_epoch)
Remember to enter "Date Format" (you need to scroll down to find it), otherwise, you can't upload the Dataflow.
Side note: toDate() is case-sensitive.
Reference:
Thank you. This post was just what I needed when I was really stuck.
ReplyDeleteglad to hear it is useful.
DeleteThis was very helpful! However, I have records where the datetime is blank and then I end up with a false date after the conversion. Any idea how I can keep this null?
ReplyDeleteyou can use "case when" in the formula expression
DeleteThis comment has been removed by the author.
ReplyDeletecan anyone tell me how to convert a text field to a date field in einstein dataflow ?
ReplyDeleteuse toDate()
DeleteThis comment has been removed by a blog administrator.
ReplyDeletecan anyone help me in converting a text field to date field by compute expression ?
ReplyDeletemy date format coming in from Google Analytics is ga_date = yyyyMMdd.
ReplyDeleteHow do I convert a string like this that isn't recognized? I've done the standard epoch conversion but my dates don't always line up if a month has more then 30 days or less then 30. Do I then just do a case statement, where month = 2 then epoch for 28 days where month = 8 then epoch for 31 days
((string_to_number(substr(ga_date, 1,4))-1970)* 31556926) + ((string_to_number(substr(ga_date, 5, 2))-1)* 2629743) + ((string_to_number(substr(ga_date, 7))-1)* 86400)