Skip to main content
Solved

Use date user published parameters in SQLCreator

  • March 6, 2019
  • 4 replies
  • 75 views
tsirkuse
Contributor
Forum|alt.badge.img+3

Hello,

I am reading my oracle non spatial database with an SQL query containing couple of dates. When inputting the dates directly in the query, there is no problem and it runs smoothly.

I would like to make user parameters from these dates. So I have created published user parameters choosing 'date' as type.

I then try to use the parameter in my SQL query (where clause) but I get a type error over and over. I have tried couple of different notations but it doesn't seem to work...

This works (without parameters)

 

AUTO_START_DATE <= TO_DATE('15/12/2019', 'DD/MM/YYYY')

Then I tried several variation to use :

 

$(START_DATE)

but I can't seem to find the right syntax.

Thank you

Best answer by erik_jan

Did you try AUTO_START_DATE <= TO_DATE('$(START_DATE)', 'YYYY-MM-DD')

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

4 replies

erik_jan
Contributor
Forum|alt.badge.img+22
  • erik_janContributor
  • Contributor
  • 2179 replies
  • Best Answer
  • March 6, 2019

Did you try AUTO_START_DATE <= TO_DATE('$(START_DATE)', 'YYYY-MM-DD')

david_r
Celebrity
  • david_rCelebrity
  • 8394 replies
  • March 6, 2019

The DATE-type parameter returns an "FME date" string, so you should use

to_char('$(START_DATE)', 'YYYYMMDD')

Notice the form YYYYMMDD.

ebygomm
Influencer
Forum|alt.badge.img+44
  • ebygommInfluencer
  • Influencer
  • 3429 replies
  • March 6, 2019

The parameter is stored in FME format, i think you will need to use something like

AUTO_START_DATE <= TO_DATE($(START_DATE), 'YYYYMMDD')
erik_jan
Contributor
Forum|alt.badge.img+22
  • erik_janContributor
  • Contributor
  • 2179 replies
  • March 6, 2019

Did you try AUTO_START_DATE <= TO_DATE('$(START_DATE)', 'YYYY-MM-DD')

Did some testing and @egomm is right.

It should be:

AUTO_START_DATE <= TO_DATE('$(START_DATE)', 'YYYYMMDD')

Community Stats

32,083
Posts
121,825
Replies
39,717
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
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.