Skip to main content
  • EN

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

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.

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'

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

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:

 

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:

 

 

 

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

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:

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 🙂

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.

 

 

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 🙂

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.

 

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.

 

Idea posted...

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

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.