Skip to main content
Solved

ESRI Geo DB reader where clause

  • July 21, 2018
  • 2 replies
  • 15 views
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"]
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.

2 replies

takashi
Celebrity
  • takashiCelebrity
  • 7842 replies
  • 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"]
Why not inspect features with Visual/Data Preview and Feature/Record Information before writing them into a destination dataset?
boubcher
Contributor
Forum|alt.badge.img+11
  • boubcherContributor
  • Author
  • Contributor
  • 212 replies
  • 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"]
@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 

 

Community Stats

32,058
Posts
121,723
Replies
39,686
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.