Skip to main content
  • EN
I'm getting this error below:

 

 

MS SQL Server Writer: Failed to parse `Date' from attribute value `05/07/2013' for column `EAST_current'

 

 

My source is an Excel file and I'm writing to a regular non-spatial SQL Server 2008 table.  All the columns in the SQL Server table have the datetime data type.

 

 

What could be the problem?

 

 

Do you see that single tick mark in front of the 05 in the error?  could that be a literal value?  I tried testing for that tick mark character in a Testor using the Match RegEX test using t`] and \\A[`] as the right value, but the row came out of the Failed port.  So I don't think the tick mark is really there.

 

 

The source data looks like this when I view it in the Feature Inspector while the translation is running:

 

 

5/7/2013

 

 

so I use the DateFormatter to conver the month and date to 2 digits with this date format:   %m/%d/%Y

 

 

this seems like a simple problem, i just can't see it.

 

 

Sarah
Here is an image of the translation.

 

Hi,

 

 

The tick mark is just a visualisation issue, did you try to set the source format on the date formatter?

 

Hope this helps
Yes, I have tried setting the Source format and have left it blank.  I used %D as the source format, but didn't seem to make any difference.  Maybe i'll try some different combination in the source format...

 

 

Sarah
I started to question the settings in my Writer and changed Bulk Insert from Yes to No and ran the translation.

 

 

still a failed conversion and the error in the log now reports this:

 

 

MS SQL Server Writer: 1 attribute value(s) failed conversion, and were written as NULL values

 

 

And a row of null values is written to the SQL Server table....

 

 

Stumped...
Hi Sarah,

 

 

To make FME recognize an attribute as date type, you should format the date string to 'YYYYmmdd' style. Try 'FME Date (%Y%m%d)' for 'Date Format' parameter of the DateFormatter transformer.

 

 

Takashi
Ah, you're a smart man, Takashi!

 

 

That worked, thank you!

 

 

In the DateFormatter I have the Source Format set to %m/%d/%Y and the Date Format set to %Y%m%d.

 

 

Thanks for everyone's help and suggestions!

 

 

Sarah

Reply


Community Stats

30,159
Posts
114,480
Replies
37,804
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.