I am doing the completion date subtracted by number amount(200 days) using Subtract Interval or Interval between dates using the completion date - DateTimeNow. the FME rejection code is INVALID_INPUT. Any suggestions is greatly appreciated
Page 1 / 1
Probably the completion date from the CSV is not recognised as a date. Did you use a DateTimeConverter?
Yes I am. That seems to be the place where it is be rejected out at the DateTimeCalculator which is before the DateTimeConverter.
Hi, can you provide some samples of the dates (text from the CSV) you are trying to process?
And can you share the settings of the DateTimeConverter? (unfortunately I can't open your workspace, as I'm using an older version of FME)
Hi,
I checked the CSV and the column is date format not text.
I created a ATTRIBUTECREATOR called < Closed 45 days> which is the date field in the CSV file I included. DATETIMECONVERTER is reading the <Closed 45 days> and results_datetime which should be the results from the DATETIMECALCULATOR which takes <CLOSED 45 days> and substracts a number value to get the results_datetime results, At least thats my understanding. I have also increased the duration in DATETIMECALCULATOR to 300 days which should have resulted in a large sampling of the data. Thanks for any assistance.
Thanks for the update.
The DateTimeConverter should convert from the input format to the FME format, so something like this:
CSV never contains dates, as CSV is plain text (just open it with Notepad). However lots of programs (such as Excel) will interpret the text as a date when possible.
If this does not work, please open the CSV in a text editor (like Notepad) and look at the exact way the date is formatted. Your screendump was from Excel of something akin, which does not show the exact contents of the CSV but its own interpretation.
I looked into Wordpad, it does show the date format like this (example 1/1/2023).
Because if the format was 20230101(ISO FORMAT), then FME would be able to convert. But since it is already formatted to the date format, is this reasoning for the inability to calculate. The original data was compiled from someone else,would it be beneficial to have it recompiled.
FME needs dates to be in its own datetime-format to be able to perform calculations like with the DateTimeCalculator. So you can use the DateTimeConverter to convert dates from any format (like 1/1/2023) to FME-datetime-format.
If the dates are like 1/30/2023, set the Input Format to %N/%e/%Y
%N : One- or Two-digit month, with range 1 to 12
%e : One- or Two-digit day of month, with range 1 to the last day of the month
%Y : Four-digit year, with range 0001 to 9999
/ : Regular character, used as separatorSee the complete format here.
This was the format that I set the output format to: %N/%e/%Y. I have the input format to auto detect both ISO/FME formats.
Edit: I corrected the image, by replacing %M in both DateTimeConverters to %N, as noted below by @jamesb16 .
Before you can use the DateTimeCalculator, you have to make sure the date is recognised by FME. You can use the DateTimeConverter for this. Afterwards you want to export the data to CSV, so you have to convert the date once more, with a second DateTimeConverter.
Of course there will be other transformers in your workspace, as you want to do some testing as well.
I will check into that, for all the other transformer. Mine looks like yours,except output is %N%e%Y
You are right, it should be %N instead of %M, my mistake (%M stands for two-digit minutes).
I corrected the image in my previous post.