Pages

Friday, May 4, 2018

Einstein Analytics: Convert DateTime field to Date field or Text field

This would be a simple tip for you that start using Einstein Analytics.

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

Text
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:


10 comments:

  1. Thank you. This post was just what I needed when I was really stuck.

    ReplyDelete
  2. This 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?

    ReplyDelete
    Replies
    1. you can use "case when" in the formula expression

      Delete
  3. This comment has been removed by the author.

    ReplyDelete
  4. can anyone tell me how to convert a text field to a date field in einstein dataflow ?



    ReplyDelete
  5. This comment has been removed by a blog administrator.

    ReplyDelete
  6. can anyone help me in converting a text field to date field by compute expression ?

    ReplyDelete
  7. my date format coming in from Google Analytics is ga_date = yyyyMMdd.

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

    ReplyDelete

Page-level ad