Skip to main content
Solved

Need to change date from 20190100 to 20190101

  • June 26, 2020
  • 3 replies
  • 49 views
W
Forum|alt.badge.img

I have some bad data from an outside source and some of the days are 00 and not 01 (YYYY:MM:DD). The SQL db I am trying to write is dropping these records. Stringreplacer? A little direction would be much appreciated.

One of the errors from the log:

Microsoft SQL Server Non-Spatial Writer: Failed to parse '19810400' as a datetime value. For FME datetime syntax, please see http://docs.safe.com/fme/html/FME_Desktop_Documentation/FME_Workbench/!Transformer_Parameters/standard_fme_date_time_format.htm. For ISO datetime syntax, please see http://docs.safe.com/fme/html/FME_Desktop_Documentation/FME_Workbench/!Transformer_Parameters/standard_fme_date_time_format.htm#ISO-8601-Duration-Format

2020-06-26 10:36:20| 5.2| 0.0|WARN |Microsoft SQL Server Non-Spatial Writer: Failed to parse `Date' from attribute value `19810400' for column `prc_date_last_sale'

Thanks!!

Best answer by markatsafe

@wjloy StringReplacer shoul do it. Assuming your source data looks like: YYYY:MM:DD then this configuration should do it (and also catch bad months):

If your date is already in the FME format YYYYMMDD then use a regular expression with: 00$

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, 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.

3 replies

M
Forum|alt.badge.img+2
  • markatsafe
  • 1891 replies
  • Best Answer
  • June 26, 2020

@wjloy StringReplacer shoul do it. Assuming your source data looks like: YYYY:MM:DD then this configuration should do it (and also catch bad months):

If your date is already in the FME format YYYYMMDD then use a regular expression with: 00$

W
Forum|alt.badge.img
  • wjloy
  • Author
  • 10 replies
  • June 26, 2020

@wjloy StringReplacer shoul do it. Assuming your source data looks like: YYYY:MM:DD then this configuration should do it (and also catch bad months):

If your date is already in the FME format YYYYMMDD then use a regular expression with: 00$

It is changing the blank records with 00000001, which I don't want to happen.

W
Forum|alt.badge.img
  • wjloy
  • Author
  • 10 replies
  • June 26, 2020

@wjloy StringReplacer shoul do it. Assuming your source data looks like: YYYY:MM:DD then this configuration should do it (and also catch bad months):

If your date is already in the FME format YYYYMMDD then use a regular expression with: 00$

I added an additional StringReplacer to remove 00000000 entries before replacing 01 and it works. No more SQL errors!!

Thank you!

Community Stats

32,071
Posts
121,787
Replies
39,700
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
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.