Skip to main content
Solved

ESRI Geo DB reader where clause

boubcher
Contributor
Forum|alt.badge.img+11

Helle

we are looking to extract features based on created date, we created a published parameter ( syn_date) and in the reader where clause we use this expression

( CREATED_DATE>date'2015-02-09 13:00:00') and it's working

now when we use the publish parameter to specify the date we used this expression

CREATED_DATE>date$(Sync_date) this is not working

we inspect the syn_date attribute the value show this ( 20180120000000)

and in the publish parameter its shows the right format

Any idea how to fix this

Thanks

 

GeoDB reader

inspect

Published param

Best answer by takashi

Hi @boubcher, as you have observed, the value of a user parameter with Date/Time type will be internally formatted with FME standard date/time format. i.e. "%Y%m%d%H%M%S".

A workaround is: create a private parameter with Scripted (Python or Tcl) type to convert the format to "%Y-%m-%d %H:%M:%S" and then refer the formatted date/time string from the WHERE clause parameter.

Scripted (Python) Parameter Example

from datetime import datetime
dt = datetime.strptime(FME_MacroValues['Sync_date'], '%Y%m%d%H%M%S')
return datetime.strftime(dt, '%Y-%m-%d %H:%M:%S')

Scripted (Tcl) Parameter Example

set dt [clock scan $::FME_MacroValues(Sync_date) -format "%Y%m%d%H%M%S"]
return [clock format $dt -format "%Y-%m-%d %H:%M:%S"]
View original
Did this help you find an answer to your question?

2 replies

takashi
Influencer
  • takashiInfluencer
  • Best Answer
  • July 22, 2018

Hi @boubcher, as you have observed, the value of a user parameter with Date/Time type will be internally formatted with FME standard date/time format. i.e. "%Y%m%d%H%M%S".

A workaround is: create a private parameter with Scripted (Python or Tcl) type to convert the format to "%Y-%m-%d %H:%M:%S" and then refer the formatted date/time string from the WHERE clause parameter.

Scripted (Python) Parameter Example

from datetime import datetime
dt = datetime.strptime(FME_MacroValues['Sync_date'], '%Y%m%d%H%M%S')
return datetime.strftime(dt, '%Y-%m-%d %H:%M:%S')

Scripted (Tcl) Parameter Example

set dt [clock scan $::FME_MacroValues(Sync_date) -format "%Y%m%d%H%M%S"]
return [clock format $dt -format "%Y-%m-%d %H:%M:%S"]
boubcher
Contributor
Forum|alt.badge.img+11
  • boubcherContributor
  • Author
  • Contributor
  • July 22, 2018
takashi wrote:

Hi @boubcher, as you have observed, the value of a user parameter with Date/Time type will be internally formatted with FME standard date/time format. i.e. "%Y%m%d%H%M%S".

A workaround is: create a private parameter with Scripted (Python or Tcl) type to convert the format to "%Y-%m-%d %H:%M:%S" and then refer the formatted date/time string from the WHERE clause parameter.

Scripted (Python) Parameter Example

from datetime import datetime
dt = datetime.strptime(FME_MacroValues['Sync_date'], '%Y%m%d%H%M%S')
return datetime.strftime(dt, '%Y-%m-%d %H:%M:%S')

Scripted (Tcl) Parameter Example

set dt [clock scan $::FME_MacroValues(Sync_date) -format "%Y%m%d%H%M%S"]
return [clock format $dt -format "%Y-%m-%d %H:%M:%S"]
@takashi

 

 

 

Excellent Takashi , I did also another workaround by creating 3 user parameter, year, Month, and day 

 

CREATED_DATE>=date'$(Sync_Year)-$(Sync_Month)-$(Sync_day) 00:00:00'

 

 

Thanks Agaian 

 

Reply


Helpful Members This Week

Community Stats

31,191
Posts
118,241
Replies
38,799
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.