Skip to main content
  • EN

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!!

@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$

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

@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!

Reply


Community Stats

30,193
Posts
114,603
Replies
37,855
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.