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)!

tim_wood
Rank
Badge +7

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).

icon

Best answer by takashi 7 April 2017, 14:06

View original

13 replies

takashi
Rank
Userlevel 2
Badge +17

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.

david_r
Rank
Userlevel 4

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
Rank
Badge +7

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

david_r
Rank
Userlevel 4

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
Rank
Userlevel 4

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
Rank
Badge +7

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

david_r
Rank
Userlevel 4

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
Rank
Userlevel 4

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
Rank
Badge +7

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
Rank
Userlevel 4
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
Rank
Badge +7

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
Rank
Userlevel 4
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
Rank
Badge +7

Idea posted...

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

Reply


Community Stats

28,707
Posts
109,479
Replies
36,553
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.