Error converting date from txt file
‘Conversion failed when converting date and/or time from character string’.
Table column is defined as: [DocDate] [date] NULL
Any help appreciated!
You will need to create a calculated field that translates the date.
Something like this, where we separate the string and add in the separators, with the assumption that every date has 6 characters and it’s month/day/year. Replace _MYCOLUMN with the name of the column from your data source.
return _MYCOLUMN.Substring(0,2) & “-” & _MYCOLUMN.Substring(2,2) & “-” & _MYCOLUMN.Substring(4,2)
Thanks for the response and suggestion Lorren. I tried your suggestion but still am not able to run successfully. The date column is defined as [DocDate] [date] NULL, in the table. In SmartConnect, I’ve tried the defining the date mapping with convert and calculated fields but still get errors referencing ‘integer’. When I use your suggestion the message reads: ‘Error in calculation column DATEFORMAT ‘substring’ is not member of ‘integer’. Of the multiple convert & calculated options I’ve tried I believe they all have referenced ‘integer’.
You get this new integer error because your field looks like an Integer since it is just number fields. So ODBC says “integer” and not “string”.
On the source, you need to press the Schema button and go into the schema and define this field as a char (255) field and not the default “integer” that it was guessed as.
Once you do that, the field will now be a string and the substring() function will work as expected.
If dashes don’t work, switch to “/” instead as MM/DD/YYYY is more typical that MM-DD-YYYY
If you would like to submit an answer or comment, please sign in to the eOne portal.