Skip to main content
Solved

dd/mm/yyyy hh:mm:ss date time values in CSV not being formatted by DateFormatter

tim_wood
Contributor
Forum|alt.badge.img+8

Using FME 2017.0 (17271).

I've got some CSV files (CSV2 Reader) with dates in the format dd/mm/yyy hh:mm: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!

Best answer by david_r

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

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

6 replies

david_r
Celebrity
  • david_rCelebrity
  • Best Answer
  • April 5, 2017

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

redgeographics
Celebrity
Forum|alt.badge.img+49

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.

redgeographics
Celebrity
Forum|alt.badge.img+49
redgeographics wrote:

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.

 

 

tim_wood
Contributor
Forum|alt.badge.img+8
  • tim_woodContributor
  • Author
  • Contributor
  • April 5, 2017

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.

takashi
Influencer
  • takashiInfluencer
  • April 5, 2017

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.

tim_wood
Contributor
Forum|alt.badge.img+8
  • tim_woodContributor
  • Author
  • Contributor
  • April 5, 2017
takashi wrote:

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


Helpful Members This Week

Community Stats

31,259
Posts
118,543
Replies
38,849
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.