Hello. I realize there are several different date range topics, and I can't seem to find one that applies to my needs.
My goal is to query or tag records within 90-, 270-, 365-day date ranges based on CurrentDate. I have not been able to get anything to work with FME tools, but normally the SQL query would be:
Select * from [layer] where "expiration_date" >= CurrentDate+90
Any ideas?
Thanks.
Best answer by hollyatsafe
Hi @ronaldmcoker
In order to perform date calculations in FME I believe your date attribute will need to be in FME format - you can use the DateTimeConverter to do this.
You can then use the DateTimeStamper to get todays date and then use the DateTimeCalculator as mentioned already.
Alternatively you can perform date calculations in any text editor using the datetime functions. E.g in a tester set the left value to your expirydate the operator to <= and right value to:
@DateTimeAdd(@Value(_timestamp),P90D)And the mode to Date/time. The data will then come out of the passed or failed ports accordingly.
+41Hi @ronaldmcoker
There is a transformer DateTimeCalculator.
It could be interesting for you.
Thanks,
Danilo
Hi @ronaldmcoker
In order to perform date calculations in FME I believe your date attribute will need to be in FME format - you can use the DateTimeConverter to do this.
You can then use the DateTimeStamper to get todays date and then use the DateTimeCalculator as mentioned already.
Alternatively you can perform date calculations in any text editor using the datetime functions. E.g in a tester set the left value to your expirydate the operator to <= and right value to:
@DateTimeAdd(@Value(_timestamp),P90D)And the mode to Date/time. The data will then come out of the passed or failed ports accordingly.
Hi @ronaldmcoker
In order to perform date calculations in FME I believe your date attribute will need to be in FME format - you can use the DateTimeConverter to do this.
You can then use the DateTimeStamper to get todays date and then use the DateTimeCalculator as mentioned already.
Alternatively you can perform date calculations in any text editor using the datetime functions. E.g in a tester set the left value to your expirydate the operator to <= and right value to:
@DateTimeAdd(@Value(_timestamp),P90D)And the mode to Date/time. The data will then come out of the passed or failed ports accordingly.
Hi @ronaldmcoker
In order to perform date calculations in FME I believe your date attribute will need to be in FME format - you can use the DateTimeConverter to do this.
You can then use the DateTimeStamper to get todays date and then use the DateTimeCalculator as mentioned already.
Alternatively you can perform date calculations in any text editor using the datetime functions. E.g in a tester set the left value to your expirydate the operator to <= and right value to:
@DateTimeAdd(@Value(_timestamp),P90D)And the mode to Date/time. The data will then come out of the passed or failed ports accordingly.
Hi @ronaldmcoker
In order to perform date calculations in FME I believe your date attribute will need to be in FME format - you can use the DateTimeConverter to do this.
You can then use the DateTimeStamper to get todays date and then use the DateTimeCalculator as mentioned already.
Alternatively you can perform date calculations in any text editor using the datetime functions. E.g in a tester set the left value to your expirydate the operator to <= and right value to:
@DateTimeAdd(@Value(_timestamp),P90D)And the mode to Date/time. The data will then come out of the passed or failed ports accordingly.
I've attached my translation if you'd like to review (20180710-datequeries-testing.fmw).
*****
Made some changes to Date only (instead of Datetime conversions) and changed the Tester mode to Date/Time.
"Tester(TestFactory): TestFactory: Unable to do a date/time comparison of '20120527' and 'DateTimeAdd(20180711, P90D)'. Comparing as strings instead"
Hi @ronaldmcoker
In order to perform date calculations in FME I believe your date attribute will need to be in FME format - you can use the DateTimeConverter to do this.
You can then use the DateTimeStamper to get todays date and then use the DateTimeCalculator as mentioned already.
Alternatively you can perform date calculations in any text editor using the datetime functions. E.g in a tester set the left value to your expirydate the operator to <= and right value to:
@DateTimeAdd(@Value(_timestamp),P90D)And the mode to Date/time. The data will then come out of the passed or failed ports accordingly.
For the date/time functions to work they need to be preceded by @ . Instead of typing this in by hand in the text editor you can add these functions from the menu on the left hand side - please see picture attached.
For the date/time functions to work they need to be preceded by @ . Instead of typing this in by hand in the text editor you can add these functions from the menu on the left hand side - please see picture attached.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
