SQL Server datetime field to Salesforce System.DateTime
Dan asked 5 years ago
I’m trying to move data from a CRM to Salesforce – source is an SQL Server 2012 database. I can see in SmartConnect that the field is a ‘datetime’ field, and that in Salesforce the custom field I created is a ‘System.DateTime’ field.
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!
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!
Answers
Dan answered 5 years ago
Hi Chris,
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:
Modify screen
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.
Any thoughts?
Thanks so much!
Dan
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:
Modify screen
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.
Any thoughts?
Thanks so much!
Dan
Dan replied 5 years ago 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:
Modify screen: https://drive.google.com/file/d/1WrF1xJiDu8vaOWUX7hCqv8ROX9Kw_mum/view?usp=sharing
Addition Fields screen:
https://drive.google.com/file/d/1XzSG6QWnbA_RfTyCJ40DQDSQpHOyurBu/view?usp=sharing
Ethan Sorenson Staff answered 4 years ago
Dan,
We now have a help article that explains the format expected by SalesForce.
https://www.eonesolutions.com/help-article/salesforce-date-field-imports/
We now have a help article that explains the format expected by SalesForce.
https://www.eonesolutions.com/help-article/salesforce-date-field-imports/
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:
https://www.eonesolutions.com/Manuals/SmartConnect/SmartConnect%202018/?page=sc_date_column
Hi Chris,
Thanks for the answer. My full response is below (just in case).