Question

Oracle TO_DATE function into Where Clause

  • 10 December 2019
  • 1 reply
  • 47 views
philippeb
Badge +2

Hello,

I have a table with millions of features. I need to use the Where Clause to extract what I want.

I'm using a Published Parameter to define a date. My problem is the date format into database (YYYY-MM-DD). And Data Inspector tells me it's encoded windows-1252, not string (don't know if it changes something).

I though to use into Where Clause the TO_DATE function to convert my dates. 

 TO_DATE('@Value(DATE_into_Database)', 'YYYYMMDD') => '$(DATE_FROM)'

But it's not working. I get an Unable to execute SQL statement. And I'm not even sure if it's supported by FME because this function isn't into the SQL Date Functions choices. I only have those (the help button sent me there) : SQL Date Functions 

Do you have an idea how I could extract those dates ?

1 reply

erik_jan
Rank
Userlevel 2
Badge +12

I believe the published parameter is a date in char form YYYYMMDD:HHMINSS

So the easiest way would be converting the database date to a char, like this (assuming the DATE_into_Database is an Oracle table attribute):

TO_CHAR(DATE_into_Database, 'YYYYMMDD') => '$(DATE_FROM)'

Hope this helps.

Reply


Community Stats

28,670
Posts
109,366
Replies
36,531
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.