Skip to main content
Solved

MS SQL Server Writer: Failed to parse `Date' from attribute value

sdepriest
Contributor
Forum|alt.badge.img+9
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 [`] 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

Best answer by takashi

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
View original
Did this help you find an answer to your question?
This post is closed to further activity.
It may be a question with a best answer, an implemented idea, or just a post needing no comment.
If you have a follow-up or related question, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

6 replies

sdepriest
Contributor
Forum|alt.badge.img+9
  • sdepriestContributor
  • Author
  • Contributor
  • May 7, 2013
Here is an image of the translation.

 

fmelizard
Contributor
Forum|alt.badge.img+17
  • fmelizardContributor
  • Contributor
  • May 7, 2013
Hi,

 

 

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

 

Hope this helps
sdepriest
Contributor
Forum|alt.badge.img+9
  • sdepriestContributor
  • Author
  • Contributor
  • May 7, 2013
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
sdepriest
Contributor
Forum|alt.badge.img+9
  • sdepriestContributor
  • Author
  • Contributor
  • May 7, 2013
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...
takashi
Influencer
  • takashiInfluencer
  • Best Answer
  • May 8, 2013
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
sdepriest
Contributor
Forum|alt.badge.img+9
  • sdepriestContributor
  • Author
  • Contributor
  • May 8, 2013
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

Helpful Members This Week

Community Stats

31,137
Posts
118,048
Replies
38,777
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

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