Skip to content
+1-888-319-3663

COMMUNITY FORUM

SQL Server datetime field to Salesforce System.DateTime

Dan asked 3 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!
Chris Dew replied 3 years ago

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

Dan replied 3 years ago

Hi Chris,

Thanks for the answer. My full response is below (just in case).

Answers
Dan answered 3 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
 
Dan replied 3 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 3 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/
 

If you would like to submit an answer or comment, please sign in to the eOne portal.