Failed to convert parameter value from a String to a Date/DateTime

The error message ‘Failed to convert parameter value from a String to a Date or DateTime’ has multiple possible causes. We will explore all the major causes in this article for both SmartConnect on premise and the online option.

1. Make sure all fields that are expecting a date are getting one in a proper format. This is an example of what you may be looking for.

2. On older versions of SmartConnect, dates wouldn’t always read correctly from Excel data sources. To find out if this is the issue, you can use a calculated field to display the date.
   1. Go into your integration

2. Open the node you are trying to edit
3. Go to the Additional Columns tab at the top of your screen

4. Select Calculated from the drop-down

5. Find the source that has the date under your source columns

6. For the calculation, drag your source column into the parentheses of the message Box and after the return

MessageBox.Show(_EXAMPLE)
return _EXAMPLE

7. Make sure you assigned a name to this column and press OK to close this window
8. Assign this calculation to the date field you are trying to map to.

9. Press OK in the top left again and run the integration. You should see a window pop up like this.

If it is formatted correctly it should look like this. You might want to remove the MessageBox.Show After confirming this is in the correct format.

10. If it is formatted incorrectly, it may look like this or many other different formats

This format is incorrect because GP was expecting Month/Day/Year but we passed in Day/Month/Year

11. SmartConnect should be able to read the date correctly as long as it is in the correct order. You can check what order SmartConnect is expecting by going to the top right File-> Maintenance -> Setup and you will see a screen that shows you the order SmartConnect wants the Dates and Times in.

3. After those first two steps, you may see that your date shows up correctly but you are still getting the error. Then you may need to change the date format. There are two ways you can do this. You can use a Date Calculation Column or regular Calculation.
   1. For Date Calculation, you will go to Addition Columns and click Date Column

2. Here you can convert any date format to a different date format. For example:

3. The other option would be to use a calculation to change the date. Here is an example of how you can do that

dim DocDate as Date = _EXAMPLE
dim finalDate as String
finalDate = DocDate.ToString("yyyy/dd/MM")
MessageBox.Show(finalDate)
return finalDate

4. Once this calculation is successful you will want to remove the MessageBox.Show to stop the window from popping up while running the integration

SmartConnect.com converting a bad date

SmartConnect online version will be very similar for all of the step mentioned above except for the scripting language used to write the calculation. SmartConnect online uses JavaScript for it’s scripting language because of this we will need to adjust our scripts.

1. There is no .ToString method in JavaScript that we can use to define a format for our date. so it will be very difficult to change the order of the date from MM/DD/YYYY to DD/MM/YYYY for example, you should use the date calculation in additional columns if you need to change the format.
2. If you have extra characters on the date for example ’02/01/2024 07:27:39 Thursday’ and would like to remove the time and the and the ‘Thursday’ and only pass in the date you can use this script.
3. This script takes your source column and find the second instance of the backslash (if your date comes in with dashes you can switch the / to a -) and counts 4 more characters (if your year is only two character long change the 5 to a 3) and then it creates a new substring that you will return at the end of the script.
   
   var sourceDate = this._{YOUR_SOURCE_COLUMN};

var secondSlashIndex = sourceDate.indexOf('/', sourceDate.indexOf('/') + 1);

var finalDate = sourceDate.substring(0, secondSlashIndex + 5);

return finalDate;
   
4. This script will also ensure that if you date comes in as ‘1/2/2024′ one day and ’11/2/2024’ that it will still create the correct substring as it bases it off the index of the second slash which is the one before the year, so as long as your year format doesn’t change this script should work.
   
   If you are still having the error after going through the article you can reach out to support at support@eonesolution.com and we will be able to assist you in finding a solution.