Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

DateTime formatting in Salesforce Output Tool

snjohnston
5 - Atom

Hi all,

 

I am trying to import data into a DateTime field in Salesforce, but I keep getting a field type error. From what I've read on the Salesforce forums, acceptable formats for loading DateTime data include:

  • yyyy-mm-dd hh:mm:ss
  • yyyy-mm-ddThh:mm:ssZ
  • yyyy-mm-ddThh:mm:ss.sssZ

However, I can't seem to get it to work. If I try either of the last two formats, it seems like Alteryx reads in the numerical Excel date value instead of the formatted result, I also tried converting the file to a CSV to force the formatting, but it still gave me the same formatting error. 

 

This is the formatted date I'm trying to pass in: 

2015-09-28T21:43:36.000z

 

And this is the error:

Error: Salesforce Output (2): Record-level error from Salesforce API: "INVALID_FIELD:Failed to deserialize field at col 8. Due to, '42275.9052777778' is not a valid value for the type xsd:dateTime:JBCXM__ResponseDate__c --"

 

Anyone have a solution? 

 

Thanks in advance for your help!

 

Shannon

 

 

16 REPLIES 16
michael_treadwell
ACE Emeritus
ACE Emeritus

If I interpret your problem correctly, you are reading in a file (.xlsx or .csv) into Alteryx with a date field of the format 2015-09-28T21:43:36.000z. Then you are attempting to use the Salesforce Output tool to output the data.

 

If that is the case it might just be that Alteryx is reading your date column as a string because of the format. There is a DateTime Parse tool that can help in this case.

 

Check to see if the attached module helps. If not, it may just be that I do not fully understand your issue.

NeilR
Alteryx Alumni (Retired)

Hi Shannon,

 

Your solution is VERY close to working - you just need to capitalize the "Z". The following date should work: 2015-09-28T21:43:36.000Z

We're using the BULK API for uploading records to SFDC so the pertinent documentation is here.

That being said, the Salesforce Output tool should probably be automatically converting Alteryx dates to this format, which it is not. I will see if we can build this feature into a future version of the tool.

 

Neil

JD1607
5 - Atom

Hi Neil, 

 

I've just experienced a similar issue to the original poster. Your explanation makes sense but I can't seem to actually format my date like this in Alteryx in a way which will work in the Salesforce output tool. 

 

My date field is generated in the Alteryx workflow (with a Formula tool) using ‘DateTimeFirstOfMonth()’. I’ve tried to format it in a number of different ways but I can’t get it to work and the following error message comes up on the Salesforce output tool when I run the workflow:

Record-level error from Salesforce API: "INVALID_FIELD:Failed to deserialize field at col 2. Due to, '2017-01-01 00:00:00' is not a valid value for the type

 

I'd really appreciate help on this!

 

Thanks, 
James

 

 

NeilR
Alteryx Alumni (Retired)

You'll need to create a new string field to match to format above. You can use a formula tool to create the string field based on your datetime field. Here's an example of an expression that will work to create the necessary format:

Left([datetime_orig], 10) + 'T' + Right([datetime_orig], 8) + '.000Z'

Capture.PNG

JD1607
5 - Atom

Thanks a lot for the help! That's worked. My issue was that I was using V_String and not V_WString when converting the format of my DateTime to 

yyyy-MM-ddTHH:mm:ss.SSSZ. 

 

Thanks, 

James

MP_FMI
5 - Atom

I followed the above steps to generate a string to represent date in  'yyyy-mm-ddThh:mm:ss.sssZ' format.

I am facing issues to convert this string generated to datetime datatype. 

Any suggestions will be appreciated.

brendafos
10 - Fireball

I'm using the new Salesforce Output tool you get from the Gallery for 2018.3. And now I am getting the Salesforce Bulk API error about my date values I'm trying to load into Salesforce.

When I use the older Salesforce Output it does not give this error.

 

I am not starting with CSV data.  My data source is a database with data types for date.  So I'm not going from String type to Date Type.

 

Here's the error:

Error: Salesforce Output (639): Record-level error from Salesforce API: "INVALID_FIELD:Failed to deserialize field at col 4. Due to, 'T.000Z' is not a valid value for the type xsd:date:Termination_Date__c --"

 

I must figure out how to fix this. Please help.

 

 

 

SophiaF
Alteryx
Alteryx

@brendafos the new Output tool is using the Bulk API (unlike the older version). According to the API documentation, the following date/date-time fields are acceptable:

 

Date Formats

We recommend you specify dates in the format yyyy-MM-ddTHH:mm:ss.SSS+/-HHmm:

• yyyy is the four-digit year

• MM is the two-digit month (01-12)

• dd is the two-digit day (01-31)

• HH is the two-digit hour (00-23)

• mm is the two-digit minute (00-59)

• ss is the two-digit seconds (00-59)

• SSS is the three-digit milliseconds (000-999)

• +/-HHmm is the Zulu (UTC) time zone offset

 

The following date formats are also supported:

• yyyy-MM-dd'T'HH:mm:ss.SSS'Z'

• yyyy-MM-dd'T'HH:mm:ss.SSS Pacific Standard Time

• yyyy-MM-dd'T'HH:mm:ss.SSSPacific Standard Time

• yyyy-MM-dd'T'HH:mm:ss.SSS PST

• yyyy-MM-dd'T'HH:mm:ss.SSSPST

• yyyy-MM-dd'T'HH:mm:ss.SSS GMT-08:00

• yyyy-MM-dd'T'HH:mm:ss.SSSGMT-08:00

• yyyy-MM-dd'T'HH:mm:ss.SSS -800

• yyyy-MM-dd'T'HH:mm:ss.SSS-800

• yyyy-MM-dd'T'HH:mm:ss

• yyyy-MM-dd HH:mm:ss

• yyyyMMdd'T'HH:mm:ss

• yyyy-MM-dd

• MM/dd/yyyy HH:mm:ss

• MM/dd/yyyy

• yyyyMMdd

 

If you are unable to get one of these formats working, could you let us know which one you are using so I can dig further?

 

Thanks!

Sophia Fraticelli
Senior Solutions Architect
Alteryx, Inc.
brendafos
10 - Fireball

Thanks you for your reply.

My dates are really just dates and not datetimes?
Do I need to make them datetime for the Saleforce Bulk API to work?

Labels