Solved

Converting Dates that does not contain days

  • 17 November 2021
  • 6 replies
  • 8 views
J

Hello,

 

I am trying to convert text strings like Jan-2019 to date . The text strings don't have the day values.

 

I have tried using DateTimeConverter transformer and the date time functions in AttributeManager but it doesn't work when I specify %b-%Y.

 

However, with the DateTimeConverter, when I convert text values like Jan-05-2019 using %b-%e-%Y , it is correctly converted to 20190105 .

 

How can I achieve the same to convert the dates like Jan-2019 without the day value to 201901 ?

 

Thanks

icon

Best answer by nielsgerrits 17 November 2021, 06:52

View original

6 replies

nielsgerrits
Rank
Userlevel 6
Badge +32

You could add "1-" to "Jan-2019" to create "1-Jan-2019", use the DateTimeConverter to create "20190101" and then remove the last 2 characters with a SubstringExtractor to have "201901" as a result.

J

You could add "1-" to "Jan-2019" to create "1-Jan-2019", use the DateTimeConverter to create "20190101" and then remove the last 2 characters with a SubstringExtractor to have "201901" as a result.

Thanks for your suggestions. Had thought of something similar with string transformers. However, I was not sure whether that would cause issues later when I need to use the date.

 

Would the string "201901" obtained this way be treated as Date by FME if I need to use it later say to calculate number of months between two dates ? I could again convert the dates back to "20190101" by adding "1". But was wondering if there is a simpler way.

nielsgerrits
Rank
Userlevel 6
Badge +32

Thanks for your suggestions. Had thought of something similar with string transformers. However, I was not sure whether that would cause issues later when I need to use the date. 

 

Would the string "201901" obtained this way be treated as Date by FME if I need to use it later say to calculate  number of months between two dates ?  I could again convert the dates back to "20190101" by adding "1". But was wondering if there is a simpler way.

If I try to add a year to "201901" the DateTimeCalculator returns a Rejected Feature. From the log:

DateTimeCalculator: Failed to evaluate expression '@DateTimeAdd(201901,P1Y)'.  Result is set to null

So apparently "201901" is not a valid date in FME.

mark2atsafe
Rank
Userlevel 4
Badge +25

Yes, unfortunately, it looks like FME won't support what you ask. From the documentation:

...temporal information must satisfy the following requirements, or else it is rejected and the result is set to null:
 
- Year, month, day are provided together, in any order, through a combination of format flags such as %Y-%m-%d.

As Niels says, the only good way is to add 01 as the day. However, you don't need to use any extra transformers to do that; you can simply set the DateTimeConverter input format to 01-%b-%Y and the output format to %Y%m

 

You'd need to keep the day to use the DateTimeCalculator, unfortunately. But I'll raise this with our developers because I think "month-year" date support would be useful.

mark2atsafe
Rank
Userlevel 4
Badge +25

Yes, unfortunately, it looks like FME won't support what you ask. From the documentation:

...temporal information must satisfy the following requirements, or else it is rejected and the result is set to null:
 
- Year, month, day are provided together, in any order, through a combination of format flags such as %Y-%m-%d.

As Niels says, the only good way is to add 01 as the day. However, you don't need to use any extra transformers to do that; you can simply set the DateTimeConverter input format to 01-%b-%Y and the output format to %Y%m

 

You'd need to keep the day to use the DateTimeCalculator, unfortunately. But I'll raise this with our developers because I think "month-year" date support would be useful.

So I suggested that we allow partial dates in the DateTimeConverter.

 

However, it doesn't mean that we'd have full native support for partial dates. i.e. the DateTimeCalculator won't handle them (because it doesn't have a field for flags like the Converter) and writers won't handle them either (you could write to a string, but not to a date/datetime field).

 

The issue is filed as FMEENGINE-10149

 

What I might do is try to create a custom transformer for the hub that handles partial date calculations (a PartialDateTimeCalculator as it were).

J

Thanks @mark2atsafe​  for raising the issue. It would be nice to have partial dates supported.

Thanks @nielsgerrits​ for the suggestions.

 

The dates in my datasets are associated with monthly values and having a specific day (eg. 1st of the month) doesn't quite represent the relationship in the data. This would likely confuse someone looking at the data.

However, for now I have added 01 as the day to be able to use the text values as Date.

 

Thanks for the suggestions.

Reply


Community Stats

28,688
Posts
109,425
Replies
36,543
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.