Skip to main content
Solved

Need to change date from 20190100 to 20190101

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$

View original
Did this help you find an answer to your question?

3 replies

M
Forum|alt.badge.img+2
  • markatsafe
  • 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
  • June 26, 2020
markatsafe wrote:

@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
  • June 26, 2020
markatsafe wrote:

@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


Helpful Members This Week

Community Stats

31,201
Posts
118,277
Replies
38,804
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.