Skip to content
+1-888-319-3663

COMMUNITY FORUM

Date errors Importing from Access Database.

Bill asked 3 years ago
Using SmartConnect version 20.17.0.17
Data source is an Access DB query.  Field format is string.  (10292018, 11012018, etc). 
When using the source column for the mapping, I get the error: “Failed to convert parameter value from a String to a DateTime.”  
I have tried the solutions I found here (use a calculated field) with no success.
Any ideas?
Thanks!
Bill replied 3 years ago

Adding one other note… this same data source works fine with Integration Manager.

Answers
Lorren Zemke Staff answered 3 years ago
Bill, when sending this format of a date to SQL, it cannot parse that string since there are no delimiters. SQL doesn’t know if the date you provide is supposed to be month,day,year or year,month,day so it throws an error.
You will need to create a calculated field from the Additional Columns menu.
In that calculated field you can use the following script where MYCOLUMN would be replaced with the name of your source column
return new DateTime(_MYCOLUMN.Substring(4,4), _MYCOLUMN.Substring(0,2), _MYCOLUMN.Substring(2,2)).ToString(“yyyy-MM-dd”)
 
This script parses the string into a date and then returns it as a formatted date string to SQL.
Once the script is validated and saved, you can map it to your date field.

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