Skip to content
+1-888-319-3663

COMMUNITY FORUM

Error converting date from txt file

Debi asked 2 years ago
I’ve created a table to which I’m bringing in data from a text file.  The file dates are formatted as 020220.  When running the dataload map I get the following conversion error.
‘Conversion failed when converting date and/or time from character string’.
Table column is defined as: [DocDate] [date] NULL
Any help appreciated!
 
Answers
Lorren Zemke Staff answered 2 years ago
Debi, SQL Server cannot translate the date string into a date because their are no delimiters. 
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)
 
 
Debi replied 2 years ago

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’.

Patrick Roth Staff replied 2 years ago

Debi,

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

patrick


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