DateTime formatting in Salesforce Output Tool

[snjohnston]

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

[SophiaF]

Hi @brendafos - the bottom of the list has some date formats. Are those working for you?

[brendafos]

I am still getting the same error about the dates.

The dates come out of the source as DateTime but there is not time.  

I'm using the Formula tool with DateTimeFormat and converting to yyyy-MM-dd

DateTimeFormat([TERMINATION_DATE],"yyyy-MM-dd")

But it gives the same error - 

Error: Salesforce Output (13): 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 --"

What should I try?

[brendafos]

No I've put in a support ticket.

[LisaL]

@brendafos

That is the default behavior when converting null or empty dateTimes.  One can modify the expression within the tool's main macro to this:

If !IsNull([_CurrentField_]) then Left(ToString([_CurrentField_]),10) + 'T' + Right(ToString([_CurrentField_]),8) + '.000Z'
Else '#N/A'
Endif 

In general, nulls don't otherwise trigger errors, but don't update existing values to null either.  Per this post, use #N/A for that.

[brendafos]

LisaL - That is exactly what I did.

I opened the macro add the IF statement to check for nulls and then saved that.

And like your suggestion, I added 'toString' incase I send it non-strings or date types.

BUT in the Else I put '[_CurrentField_]' which works to put the null back, and Salesforce accepts that.

[Naveenbalaji]

I tried this but didn't work. It works Only after removal of date column in case the value is null. I am using the updated version - salesforce output 1.3.1

Any suggestions? 

[francois45p]

Hi,

There's probably an issue in the SFDC Output tool for this situation. To get it around, you need to remove the date field with null values before pushing to SFDC. In that case SFDC will insert a null value in the absence of the field.

So insert a filter tool to split your data with null or non-null date field. Then in the null date flow insert a select tool and remove the date field where you have your null values before pushing to Salesforce. It works for me.

I hope I am clear?

[francois45p]

It worked for me :-)