Question

Use date user published parameters in SQLCreator

T

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

4 replies

erik_jan
Rank
Userlevel 2
Badge +16

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

david_r
Rank
Userlevel 4

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

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

Notice the form YYYYMMDD.

ebygomm
Rank
Userlevel 1
Badge +21

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
Rank
Userlevel 2
Badge +16

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')

Reply


Community Stats

28,709
Posts
109,483
Replies
36,556
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.