Skip to main content

I’m querying databases using SQL Creator and have a Where clause date > ‘$(dateuserparameter)’.  I would like the dateuserparameter default value to be yesterday’s date, for example ‘2024-10-07’.  Is it possible to set the parameter’s default value to be yesterday’s date?

You can use a scripted parameter to do this. They use python, so the configuration will be something like:

from datetime import datetime, timedelta

yesterday = datetime.now() - timedelta(1)
yesterday_str = yesterday.strftime('%Y-%m-%d')
return yesterday_str

The parameter configuration would look something like:

 


Great suggestion by @hkingsbury . If you want to make yesterdays date the default, but still allow the user’s input, you can modify the scripted parameter as follows:

from datetime import datetime, timedelta
import fme

user_input = fme.macroValues.get("dateuserparameter")
if user_input:
return user_input
else:
yesterday = datetime.now() - timedelta(1)
yesterday_str = yesterday.strftime('%Y-%m-%d')
return yesterday_str

You can then reference this scripted parameter in your SQLCreator. It will return the user input, if available, otherwise yesterdays date.


Great suggestion by @hkingsbury . If you want to make yesterdays date the default, but still allow the user’s input, you can modify the scripted parameter as follows:

from datetime import datetime, timedelta
import fme

user_input = fme.macroValues.get("dateuserparameter")
if user_input:
return user_input
else:
yesterday = datetime.now() - timedelta(1)
yesterday_str = yesterday.strftime('%Y-%m-%d')
return yesterday_str

You can then reference this scripted parameter in your SQLCreator. It will return the user input, if available, otherwise yesterdays date.

This worked perfectly!  Thank you so much. 


I’m looking to set the default value of a parameter (START_DATE, type = datetime) using a scripted parameter working in Form v2023.2.2 (hoping to publish to Flow v2023). 

After setting the default value of the parameter to point to the scripted parameter for its value and then running the workbench the scripted parameter is not being evaluated and errors out.  The error occurs because the START_DATE parameter does not have a valid value. It appears the default value is not evaluated as a script but rather as a simple text.   

Anyone else been able to do this?

 


You’re returning a Python datetime object, which is not supported by FME scripted parameters. You’ll have to return a string, or a string-like object. You can use the strftime() method on your datetime object to convert it to a string.


Hi David.

I changed my script as follows but it didn’t change the result.  It still see the parameter name as the string which is invalid.

import fme
from datetime import datetime, timedelta

stdt = datetime.now() + timedelta(days=-1)
cdt = stdt.strftime("%Y%m%d%H%M%S")

return cdt

 

ERROR : Python Exception <ValueError>: time data '$(DEFAULT_START_TIME)' does not match format '%Y%m%d%H%M%S'
 


I assume that the exception is not coming from the scripted parameter itself, because your code looks correct. In that case, can you please give us more information on where the error is occurring.


The error indicates that the problem is with the START_DATE_GMT parameter.  This is in another scripted parameter which uses the input START_DATE datetime to get the corresponding GMT datetime, which will be used in the workspace to read records in AGOL keyed by start and end datetimes.  The START_DATE value default value is provided by the DEFAULT_START_TIME parameter. The script for the START_DATE_GMT parameter is shown in the error logged in FME as below. 

But it begins with the DEFAULT_START_TIME parameter not being passed to START_DATE correctly.  If I elect to select a valid start and end datetime using the widget selectors for those parameters the workbench runs without issue.

    Command line to run this workspace:
        C:\apps\FME2023_23774\fme.exe M:\documentation\FME_Workbench\FME_Server_Workbenches\FME2022\AGOL_Photo_Extraction\TEST\2024-10-04\AGOL_Photo_Extraction.fmw
          --JOB_NUMBER "IA-0296-23-J1"
          --ADD_COUNT_NUM "No"
          --COUNTER_START_NUM ""
          --SHOW_CREW_NAMES "No"
          --DATE_SORT_ORDER "Earliest to Latest"
          --START_DATE "$(DEFAULT_START_TIME)"
          --END_DATE "$(DEFAULT_END_TIME)"
          --FME_LAUNCH_VIEWER_APP "YES"
    
Starting translation...
INFORM: Using Python interpreter from `C:\apps\FME2023_23774\fmepython311\python311.dll' with PYTHONHOME `C:\apps\FME2023_23774\fmepython311'
INFORM: Python version 3.11 loaded successfully
ERROR : Python Exception <ValueError>: time data '$(DEFAULT_START_TIME)' does not match format '%Y%m%d%H%M%S'
INCLUDE -- failed to evaluate Python script `def ParamFunc():
  import fme
  from datetime import datetime, timedelta
  if fme.macroValuesi'START_DATE']:
      
      date_begin = fme.macroValuesT'START_DATE']
      DTE_date_begin = datetime.strptime(date_begin, '%Y%m%d%H%M%S')
      date_begin_GMT = DTE_date_begin + timedelta(hours=6)
      
      
  else:
      #Use default start time well before any photos were uploaded
      DTE_date_begin = datetime.strptime('1999-01-01 00:00:00', '%Y-%m-%d %H:%M:%S')
      date_begin_GMT = DTE_date_begin + timedelta(hours=6)
  return date_begin_GMT
value = ParamFunc()
macroName = 'START_DATE_GMT'
if value == None:
  return { macroName : u'' }
else:
  import six
  try:
    value = six.text_type(value)
  except UnicodeDecodeError:
    value = six.text_type(value, 'utf-8')
  return { macroName : value }
'
Program Terminating
Translation FAILED.
 


No word on this so I think it is a bug.


Reply