SQL Server datetime field to Salesforce System.DateTime
But when I run the map, I get the following error:
‘2/27/2018 12:00:00 AM is not a valid value for the type xsd:dateTime’;
I even tried changing the field type in Salesforce to Date – no change.
I tried to use CONVERT() in the SQL query to change the format of the datetime, but nothing is working. It LOOKS like the data shouldn’t have issues…
Any help would be greatly appreciated!
Here is a SalesForce KB article on proper formats: https://help.salesforce.com/articleView?id=Data-Loader-Import-data-for-Date-or-Date-Time-field-1327108684799&language=en_US&type=1
Additionally, you can use the Date Calculation column to transform from the format you have to what SalesForce requires:
Thanks for the answer. My full response is below (just in case).
Thank you for the information, but unfortunately it didn’t resolve the errors. Apparently I cannot use a CONVERT function inside the SQL, because no matter how I write that in the query, it doesn’t change the format of the data in the ‘Preview’ (and doesn’t resolve the oritinal error).
I can see that SmartConnect sees the field as a datetime field in the source system:
That screenshot is of the Modify screen in SmartConnect. The fields *are* datetime types.
The data itself, when I check Preview in Smartconnect is ‘mm/dd/yyyy’ – it looks like ‘2/1/2016’, for example.
I also tried doing calculated Date Fields, but maybe I set them up incorrectly:
Additional Fields screen
Running the mapping like that just gets me a different error:
‘Error in ‘SalesForceDestination’. Line ‘Account_createable’. Parameter ‘Contract Current End Date’. Parameter set value event. could not be converted to a date : String was not recognized as a valid DateTime.’
I have a feeling that the space above right before ‘could not be converted’ indicates a null value for the record in question.
Thanks so much!
WHY would anyone include an inline editor that allows for links to be inserted, but THEN STRIP THEM OUT? Jeez…
Here are the URLs to the two screenshots:
Addition Fields screen:
We now have a help article that explains the format expected by SalesForce.
If you would like to submit an answer or comment, please sign in to the eOne portal.