Skip to main content
Solved

WARNING & TIP! %Y%m%d%H%M%S Date/Time values in CSVs are NOT the same as FME Date/Time (%Y%m%d%H%M%S)!

  • April 7, 2017
  • 13 replies
  • 33 views
tim_wood
Contributor
Forum|alt.badge.img+8

I have CSV files with dates in the format %Y%m%d%H%M%S e.g.

Mobile Inspection,Cancelled,20140905151941,20141107130117

I'm using the CSV2 Reader in FME 2017.1 (17271) and reading in all CSV attributes as string.

In DateFormatter, if I set my Source Date Format to FME Date/Time (%Y%m%d%H%M%S) and my Destination Date Format is %d/%m/%Y %T (I've also tried %d"/"%m"/"%Y" "%T), all I get is the Invalid Date value.

However, if I set my Source Date Format to %Y%m%d%H%M%S and my Destination Date Format is %d/%m/%Y %T it works!

So even though you might think FME Date/Time (%Y%m%d%H%M%S) and %Y%m%d%H%M%S are equivalent, they're not (at least not when reading CSVs).

Best answer by takashi

I think the format "FME Date/Time (%Y%m%d%H%M%S)" for source date matches "FME Date/Time (20140905151941)", for example. That is, "FME Data/Time" and the round brackets would be interpreted as parts of the format literally.

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

13 replies

takashi
Celebrity
  • takashiCelebrity
  • Best Answer
  • April 7, 2017

I think the format "FME Date/Time (%Y%m%d%H%M%S)" for source date matches "FME Date/Time (20140905151941)", for example. That is, "FME Data/Time" and the round brackets would be interpreted as parts of the format literally.

Why not inspect features with Visual/Data Preview and Feature/Record Information before writing them into a destination dataset?
david_r
Celebrity
  • david_rCelebrity
  • April 7, 2017

Not sure what you're doing, I'm unable to reproduce:

0684Q00000ArKDnQAN.png

Result:

Attribute(encoded: utf-8)         : `TS1' has value `2014-09-05'
Attribute(encoded: utf-8)         : `TS2' has value `2014-11-07'
tim_wood
Contributor
Forum|alt.badge.img+8
  • tim_woodContributor
  • Author
  • Contributor
  • April 7, 2017

Are the date values in your CSV quoted or un-quoted?

david_r
Celebrity
  • david_rCelebrity
  • April 7, 2017

I think the format "FME Date/Time (%Y%m%d%H%M%S)" for source date matches "FME Date/Time (20140905151941)", for example. That is, "FME Data/Time" and the round brackets would be interpreted as parts of the format literally.

Ah yes, this won't work:

 

 

Whereas this works:

 

david_r
Celebrity
  • david_rCelebrity
  • April 7, 2017

Are the date values in your CSV quoted or un-quoted?

The CSV I used was copy-pasted from this post, I simply added a header with some field names:

 

 

 

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

Here's a test file and FMW that demonstrates the problem...

david_r
Celebrity
  • david_rCelebrity
  • April 7, 2017

There's your error:

See also the comment I posted under Takashi's answer.

As it is, FME would expect your CSV to contain the following:

david_r
Celebrity
  • david_rCelebrity
  • April 7, 2017

There's your error:

See also the comment I posted under Takashi's answer.

As it is, FME would expect your CSV to contain the following:

I'll just add this: if you think this is rather confusing behavior from the DateFormatter, them I'm totally with you :-)
tim_wood
Contributor
Forum|alt.badge.img+8
  • tim_woodContributor
  • Author
  • Contributor
  • April 7, 2017

I think the format "FME Date/Time (%Y%m%d%H%M%S)" for source date matches "FME Date/Time (20140905151941)", for example. That is, "FME Data/Time" and the round brackets would be interpreted as parts of the format literally.

The crucial piece of information I didn't include was that I copied & pasted FME Date/Time (%Y%m%d%H%M%S) from the Destination Date Format box because it doesn't appear in the drop-down list in the Source Date Format box.

 

 

david_r
Celebrity
  • david_rCelebrity
  • April 7, 2017
The crucial piece of information I didn't include was that I copied & pasted FME Date/Time (%Y%m%d%H%M%S) from the Destination Date Format box because it doesn't appear in the drop-down list in the Source Date Format box.

 

 

Yup, and personally I think that the DateFormatter could be a bit more intelligent about it, the current behavior isn't consistent and potentially confusing. I'll let you suggest an idea to improve it :-)
tim_wood
Contributor
Forum|alt.badge.img+8
  • tim_woodContributor
  • Author
  • Contributor
  • April 7, 2017

There's your error:

See also the comment I posted under Takashi's answer.

As it is, FME would expect your CSV to contain the following:

Agreed. The only option in the Source Date Format is unknown - automatic detection hence my copy and paste. Does anybody ever get anything else? If not I'll post an idea to add the usual date/time format options to the list.

 

david_r
Celebrity
  • david_rCelebrity
  • April 7, 2017
Agreed. The only option in the Source Date Format is unknown - automatic detection hence my copy and paste. Does anybody ever get anything else? If not I'll post an idea to add the usual date/time format options to the list.

 

No, there are no other items in that list (that I know of). A good candidate for improvement.

 

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

Idea posted...

https://knowledge.safe.com/idea/42623/add-common-datetime-formats-such-as-fme-datetime-y.html

Community Stats

32,154
Posts
122,047
Replies
39,812
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
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.