Skip to main content
Question

Date Range Query

J

I have looked at "Date Range Tester" and "Date Constraint" topics that are related to this.

I have a workspace that pulls in a non sql table. I want to be able to query this table to look for data within a date range. I created two published parameters - Start Date (11/04/2004) and End Date (11/18/2004). The work space is set to run with prompts so that the operator has to enter these two dates.

The Date Range Tester semi-works. I am getting some strange results though. My date attribute is a string as such (20041104000000.000). I trim off the empty "Time" part of the string leaving only the "Date" (20041104). I use a Tester to test for a range between 20041104 (Lower Limit, Greater than or equal to valve) and 20041118 (Upper Limit, Less than or equal to value). In the results I am getting results between 20041103 - 20041118. I don't understand why I am getting these.

Please advise if there is another option to extract this information. Any help is much appreciated.

10 replies

itay
Supporter
Forum|alt.badge.img+17
  • itaySupporter
  • Supporter
  • November 2, 2016

Hi @jimq66 I would suggest doing this querying on the table itself, this way you can efficiently get ONLY the records you need in your workspace.

Another advantage is that you dont need to deal with different date definitions in your testing.

takashi
Influencer
  • takashiInfluencer
  • November 2, 2016

Hi @jimq66, I was able to reproduce the same result as yours. The Tester in FME 2016.1.2.1 build 16674 outputs a feature having "20041103" via the Passed port with this setting. I think it's a bug in the "In range" operator. @daleatsafe, please take a look.

In the interim, try this setting instead.

takashi
Influencer
  • takashiInfluencer
  • November 2, 2016
takashi wrote:

Hi @jimq66, I was able to reproduce the same result as yours. The Tester in FME 2016.1.2.1 build 16674 outputs a feature having "20041103" via the Passed port with this setting. I think it's a bug in the "In range" operator. @daleatsafe, please take a look.

In the interim, try this setting instead.

This test clause routes "20041105" to the Failed port. We cannot use the "In range" operator.

 

ebygomm
Influencer
Forum|alt.badge.img+39
  • ebygommInfluencer
  • Influencer
  • November 2, 2016

You could also use an AttributeRangeFilter to perform the test

J
  • jimq66
  • Author
  • November 2, 2016
itay wrote:

Hi @jimq66 I would suggest doing this querying on the table itself, this way you can efficiently get ONLY the records you need in your workspace.

Another advantage is that you dont need to deal with different date definitions in your testing.

I was working on this and it just might work. @takashi solution was quick and works. I still use my parameters.

 

 

J
  • jimq66
  • Author
  • November 2, 2016
ebygomm wrote:

You could also use an AttributeRangeFilter to perform the test

Don't see a way to use the published parameters here. Am I missing something. Thanks.

 

 

J
  • jimq66
  • Author
  • November 2, 2016
takashi wrote:

Hi @jimq66, I was able to reproduce the same result as yours. The Tester in FME 2016.1.2.1 build 16674 outputs a feature having "20041103" via the Passed port with this setting. I think it's a bug in the "In range" operator. @daleatsafe, please take a look.

In the interim, try this setting instead.

Hi @takashi Is there a way to convert the input of the published parameters from this format input (11/04/2004) to this format output (20041104)?

 

takashi
Influencer
  • takashiInfluencer
  • November 2, 2016
jimq66 wrote:
Hi @takashi Is there a way to convert the input of the published parameters from this format input (11/04/2004) to this format output (20041104)?

 

once save the parameter value into an feature attribute using the ParameterFeatcher or AttributeCreator, and then convert the attribute value into the required format with the DateFormatter transformer.

 

fmelizard
Safer
Forum|alt.badge.img+19
  • fmelizardSafer
  • Safer
  • November 3, 2016
takashi wrote:

Hi @jimq66, I was able to reproduce the same result as yours. The Tester in FME 2016.1.2.1 build 16674 outputs a feature having "20041103" via the Passed port with this setting. I think it's a bug in the "In range" operator. @daleatsafe, please take a look.

In the interim, try this setting instead.

THanks for alerting us @takashi -- the team has found that there defnitely is a bug here and we're examining ways to fix it. We'll keep this thread posted.

 

takashi
Influencer
  • takashiInfluencer
  • November 9, 2016
takashi wrote:

Hi @jimq66, I was able to reproduce the same result as yours. The Tester in FME 2016.1.2.1 build 16674 outputs a feature having "20041103" via the Passed port with this setting. I think it's a bug in the "In range" operator. @daleatsafe, please take a look.

In the interim, try this setting instead.

It seems that the bug about the "In range" operator has been fixed in FME 2016.1.3.0 release build 16709.

 

Reply


Helpful Members This Week

Community Stats

31,378
Posts
119,011
Replies
39,009
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

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