Question

How do I 1. Convert dates from FME format to integers? 2. Convert dates from Date to Month i.e. from YYYYMMDD to YYYYMM

  • 22 April 2023
  • 3 replies
  • 16 views

Badge

I have hunted through docs but am struggling with dates. I can't do date comparisons in the InlineQuerier (see q linked below) so need to change dates to integers to compare using SQL, but can't figure out how to do that.

 

I also want to be able to convert dates to months for comparison e.g. testing 15 September 2022 vs 30 September 2022 should return TRUE.

 

Still new on the FME journey and any help much appreciated - or a pointer to docs. Thanks, Philip

 

Q. on dates in InlineQuerier using SQLLite

https://community.safe.com/s/question/0D54Q000080hMJ7SAM/testing-for-between-dates-supplied-by-external-file


3 replies

Userlevel 5
Badge +29

Have a look at the DateTimeConverter

 

image

Badge +20

FME datetime is a float 20230424170926.4335554

2023 04 24 17 09 26.4335554

yyyy mm dd hh mm ss.sssssss

Converting to an integer is just a matter of rounding so AttributeRounder is another choice when processing datetime. Round down for your case

Also, getting YYYYMM from FME datetime can be solved by rounding down to -8 decimal places (minus eight) then dividing the result by 10^8. By using 

@Evaluate(@floor(@Value(_timestamp),-8)/@pow(10,8))

in an AttributeManager or AttributeCreator will give you the values you need.

Badge

Late response as was ANZAC day holiday here in NZ on Tuesday.

 

Thanks very much @hkingsbury and @caracadrian for your responses. Really appreciate you taking the time, and caracadrian's comment helped me understand FME date and datetime formats.

 

Working Solution

In relation to the second part of my question I used DateTimeConverter as suggested by @hkingsbury to get both inputs in YYYY-MM-DD format (including the hyphens), which I understand is the native format of SQLite. The InlineQuerier SQL join based on date in InlineQuerier was then able to match correctly and look up values.

 

As usual with FME I'm sure there is a simpler way that uses fewer transformers but working for now.

Reply