Skip to main content
  • EN

Using FME 2017.0 (17271).

I've got some CSV files (CSV2 Reader) with dates in the format dd/mm/yyy hh🇲🇲ss e.g. 05/04/2017 14:32:21 (note space between date and time). I want to use TimeStamper and DateDifferenceCalculator so I'm trying to ensure all dates are in FME Date/Time format. I'm trying to use DateFormatter to format %d"/"%m"/"%Y" "%T to FME Date/Time but I get failures from DateDifferenceCalculator:

2017-04-05 14:36:33| 1.5| 0.3|WARN |DateDifferenceCalculator_ExpressionEvaluator: Failed to evaluate arithmetic expression '1.0*(1491399393-25*1.0/7*1.0/2014 10:58:43)*1.0/86400'

2017-04-05 14:36:33| 1.5| 0.0|WARN |DateDifferenceCalculator_ExpressionEvaluator: TCL Error Message:

2017-04-05 14:36:33| 1.5| 0.0|WARN | missing operator at _@_

2017-04-05 14:36:33| 1.5| 0.0|WARN | in expression "...393-25*1.0/7*1.0/2014 _@_10:58:43)*1.0/86400"

2017-04-05 14:36:33| 1.5| 0.0|WARN |DateDifferenceCalculator_ExpressionEvaluator: Failed to evaluate expression '1.0*(1491399393-25*1.0/7*1.0/2014 10:58:43)*1.0/86400'. Result is set to null

Help!

CSV is a pure text format, so there is no such thing as a proper date/time object, unfortunately.

Try using the following source date format:

%d/%m/%Y %H:%M:%S

Notice that there are no quotation marks.

0684Q00000ArKgeQAF.png

If you use a DateFormatter to convert it to a known date format it should work.

I used the format string %d/%m/%Y %H:%M:%S and had it converted to a FME Data/Time which the DateTimeCalculator can use.

If you use a DateFormatter to convert it to a known date format it should work.

I used the format string %d/%m/%Y %H:%M:%S and had it converted to a FME Data/Time which the DateTimeCalculator can use.

And just an added comment: you have to set a format for the source, autodetect will not properly recognize it.

 

 

Argh what an idiot! It helps if you format the actual Date Time field that you're using in the DateDifferenceCalculator...

I think the lesson is to use the "Set Invalid Date Attributes To" option in DateFormatter e.g. set it to "ERROR" (at least during development). When I wasn't seeing "ERROR" coming up in the DateDifferenceCalculator warnings in the Translation Log, I realised something might not be right upstream.

Since you are using FME 2017.0, you can also use the new features regarding date/time - the DateTimeCalculator transformer and the Date/Time Functions. e.g.

Since you are using FME 2017.0, you can also use the new features regarding date/time - the DateTimeCalculator transformer and the Date/Time Functions. e.g.

Thanks @takashi I'll check it out 🙂

Reply


Community Stats

30,129
Posts
114,377
Replies
37,768
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.