Forum Discussion

darshthakkar's avatar
darshthakkar
Valued Contributor
4 years ago
Solved

Date in Salesforce acceptable format

Hi Team, I have 2 fields, Start and End Date coming from upstream systems (i.e. snowflake) in UTC and want this to be inserted to downstream systems (i.e. Salesforce) in UTC as well. After developi...
expression_language
pipeline
salesforce
transform
  • darshthakkar's avatar
    darshthakkar
    4 years ago

    Based on how the Org has been setup in downstream systems, transformations must be made in snapLogic. For me, Salesforce Org is GMT-4 thus, I adjusted the date accordingly using

    $Expire_Date.plusHours(4).toLocaleDateTimeString({format: "yyyy-MM-dd'T'HH:mm:ss'Z'"})

    Moreover, it is vital to check the data type at the downstream systems as well. We may be ingesting wrong data and thus the dates might be off and not consistent. For example, I’m ingesting Date/Time but Salesforce Org is only Date then it wouldn’t make sense.

    Consequently, another function can also be used which is as below:

    $Expire_Date.toLocaleDateTimeString({format: "yyyy-MM-dd'T'HH:mm:ss'Z'","timeZone":"GMT"})

    $Expire_Date.toLocaleDateTimeString({format: "yyyy-MM-dd'T'HH:mm:ss'Z'","timeZone":"GMT+4"})

    Closing this thread now as the issue has been found at the downstream systems and not with snapLogic plus it has been resolved too.
    Thank you everyone for your time, help and inputs on this one.

darshthakkar's avatar
darshthakkar
Valued Contributor
4 years ago

I was putting the function at the wrong place but I was able to add the hours if I used the below string:

$Expire_Date.plusHours(4).toLocaleDateTimeString({format: "yyyy-MM-dd'T'HH:mm:ss'Z'"})

  • darshthakkar's avatar
    darshthakkar
    Valued Contributor
    4 years ago

    I’m not sure whether adding 4 hours to the transformation is a good practice as I’m a huge proponent of using time zones for ease.

    I’ll keep everyone posted on this thread if I find something interesting, until then closing this thread.

  • darshthakkar's avatar
    darshthakkar
    Valued Contributor
    4 years ago

    This works however what throws me away is the fact that it doesn’t work for a few, I’m anticipating errors on Salesforce side but is there a way I can better code this so the results remain consistent on the downstream systems as well?

    Data in Snowflake:

    Data in snapLogic going into Salesforce: (added 4 hours thus the difference in time)

    Data ingested in Salesforce:

    Expectation: Data in snowflake <==> Data ingested in Salesforce

    CC: @koryknick @ptaylor @bojanvelevski

    Regards,
    Darsh

    • darshthakkar's avatar
      darshthakkar
      Valued Contributor
      4 years ago

      Transformation rule for converting the date into the following format: 2023-02-28T00:00:00.000 EST?

      I’m currently using $Expire_Date.toLocaleDateTimeString({“format”:“yyyy-MM-dd’T’HH:mm:ss.SSS”,“timeZone”:“America/New_York”})+" EST" which is working on the output preview but unable to ingest records in the downstream systems i.e. Salesforce. I’m suspecting that the date format is causing an issue as previously without the modification of the Expire Date, the pipeline was able to ingest data downstream. Moreover, the same format of the data is able to go into Salesforce if we do it manually.

      • alchemiz's avatar
        alchemiz
        Contributor III
        4 years ago

        Hi Darsh, have you tried sending the format instead of timezone send the offset